I have the following SP:
how to see the output of query in stored Procedure
USE [expogroupdb]
GO
/****** Object: StoredProcedure [dbo].[GetUrlDataForTextFileEmail] Script Date: 8/7/2018 9:45:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUrlDataForTextFileEmail]
@currCat bigint,
@todate datetime,
@fromdate datetime,
@isDateSelected nvarchar(200),
@isRemovesSelected nvarchar(200),
@keyword nvarchar(20),
@domain nvarchar(20),
@user bigint,
@country bigint,
@region bigint,---added by chetan
@status bigint,
@urlorcatalog nvarchar(200),
@active tinyint,
@stype nvarchar(20)
--@ecount nvarchar(20)------added by chetan
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@m_currCat bigint,
@m_user bigint,
@m_country bigint,
@m_region bigint,---added by chetan
@m_status bigint,
@m_todate datetime,
@m_fromdate datetime,
@m_isDateSelected nvarchar(200),
@m_isRemovesSelected nvarchar(200),
@QUERY nvarchar(MAX) ,
@QUERY1 nvarchar(MAX) ,
@whereField nvarchar(MAX),
@whereField1 nvarchar(MAX),
@dateSelectedQuery nvarchar(MAX),
@criteriaSelected nvarchar(MAX),
@finalQuery nvarchar(MAX),
@m_keyword nvarchar(20),
@m_domain nvarchar(20),
@M_urlorcatalog nvarchar(200),
@m_active tinyint,
@m_stype nvarchar(20),
@M_urlorEmail nvarchar(50)
--@m_ecount nvarchar(20)----added by chetan
SET @m_keyword=@keyword
SET @m_domain=@domain
SET @m_currCat=@currCat
SET @m_user=@user
SET @m_country=@country
SET @m_region=@region ---added by chetan
SET @m_status=@status
SET @m_todate=@todate
SET @m_fromdate=@fromdate
SET @M_urlorcatalog=@urlorcatalog
SET @m_isDateSelected=@isDateSelected
SET @m_isRemovesSelected=@isRemovesSelected
--set @m_ecount=@ecount---added by chetan
--if @m_ecount='true'--added by chetan
---SET @QUERY='SELECT EMAIL.email FROM companyinfo COMPANY INNER JOIN emailinfo EMAIL ON COMPANY.dataID = EMAIL.DataID LEFT OUTER JOIN category CATEGORY ON COMPANY.category=CATEGORY.ID LEFT OUTER JOIN countryinfo COUNTRY ON COMPANY.country=COUNTRY.ID LEFT OUTER JOIN regioninfo REGION ON COMPANY.region=REGION.ID '
--SET @QUERY='SELECT EMAIL.email,CATEGORY.categoryname as ''category'',COMPANY.exblist as ''exb_count'' FROM companyinfo COMPANY INNER JOIN emailinfo EMAIL ON COMPANY.dataID = EMAIL.DataID LEFT OUTER JOIN category CATEGORY ON COMPANY.category=CATEGORY.ID LEFT OUTER JOIN countryinfo COUNTRY ON COMPANY.country=COUNTRY.ID LEFT OUTER JOIN regioninfo REGION ON COMPANY.region=REGION.ID '
--else--added by chetan
SET @QUERY='SELECT EMAIL.email FROM companyinfo COMPANY INNER JOIN emailinfo EMAIL ON COMPANY.dataID = EMAIL.DataID LEFT OUTER JOIN category CATEGORY ON COMPANY.category=CATEGORY.ID LEFT OUTER JOIN countryinfo COUNTRY ON COMPANY.country=COUNTRY.ID LEFT OUTER JOIN regioninfo REGION ON COMPANY.region=REGION.ID ' ---added by chetan
SET @whereField=' WHERE '
SET @criteriaSelected=''
SET @finalQuery=''
SET @m_active=@active
SET @m_active=@active
SET @m_stype=@stype
IF @m_currCat=0
SET @m_currCat=null
IF @m_isRemovesSelected='true'
SET @m_isRemovesSelected=null
IF @m_keyword='false'
SET @m_keyword=null
IF @m_user=0
SET @m_user=null
IF @m_status=0
SET @m_status=null
IF @m_country=0
SET @m_country=null
IF @m_region=0----------added by chetan
SET @m_region=null
IF @m_domain='false'
SET @m_domain=NULL
IF @M_urlorcatalog=''
SET @M_urlorcatalog=null
IF @m_stype='Email'
SET @M_urlorEmail=' EMAIL.email'
IF @m_stype='urlorcatalog'
SET @M_urlorEmail=' COMPANY.urlorcatalog'
IF @m_isDateSelected='true'
BEGIN
IF @m_currCat IS NOT NULL
BEGIN
SET @criteriaSelected=@criteriaSelected + ' COMPANY.category ='+CAST(@m_currCat AS NVARCHAR(30)) +' AND '
END
IF @m_country IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.country ='+CAST(@m_country AS NVARCHAR(30)) +' AND '
IF @m_region IS NOT NULL--------addded by chetan
SET @criteriaSelected=@criteriaSelected + ' COMPANY.region ='+CAST(@m_region AS NVARCHAR(30)) +' AND '
IF @m_status IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status ='+CAST(@m_status AS NVARCHAR(30)) +' AND '
IF @m_user IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.userid ='+CAST(@m_user AS NVARCHAR(30)) +' AND '
IF @m_isRemovesSelected IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status !=8 AND '
IF @m_keyword IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select keyword from keywordinfo where EMAIL.email like ''%''+ keyword +''%'' )) AND '
IF @M_urlorcatalog IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( '+@M_urlorEmail+' LIKE ''%''+'''+ @M_urlorcatalog +'''+''%'' OR COMPANY.companyname like ''%''+'''+ @M_urlorcatalog +'''+''%'' ) AND '
IF @m_domain IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select domainname from domaininfo where EMAIL.email like ''%@''+ domainname +''%'' )) AND '
SET @finalQuery=@QUERY + @whereField + @criteriaSelected + ' DATEADD(day, DATEDIFF(day, 0, COMPANY.date), 0) >'''+ convert(varchar(50),@m_fromdate,101) +''' AND DATEADD(day, DATEDIFF(day, 0, COMPANY.date), 0) <='''+ convert(varchar(50),@m_todate,101) +''' AND COMPANY.active ='+CAST(@m_active AS NVARCHAR(30))+' ORDER BY COMPANY.date DESC '
EXEC(@finalQuery)
print @finalQuery
--select @finalQuery
END
ELSE
BEGIN
IF @m_isRemovesSelected IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status !=8 AND '
IF @m_keyword IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select keyword from keywordinfo where EMAIL.email like ''%''+ keyword +''%'' )) AND '
IF @m_domain IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select domainname from domaininfo where EMAIL.email like ''%@''+ domainname +''%'' )) AND '
IF @M_urlorcatalog IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( '+@M_urlorEmail+' LIKE ''%''+'''+ @M_urlorcatalog +'''+''%'' OR COMPANY.companyname like ''%''+'''+ @M_urlorcatalog +'''+''%'' ) AND '
IF @m_country IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.country ='+CAST(@m_country AS NVARCHAR(30)) +' AND '
IF @m_region IS NOT NULL-------added by chetan
SET @criteriaSelected=@criteriaSelected + ' COMPANY.region ='+CAST(@m_region AS NVARCHAR(30)) +' AND '
IF @m_status IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status ='+CAST(@m_status AS NVARCHAR(30)) +' AND '
IF @m_user IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.userid ='+CAST(@m_user AS NVARCHAR(30)) +' AND '
IF @m_currCat IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.category ='+CAST(@m_currCat AS NVARCHAR(30)) +' AND '
--SET @finalQuery= @QUERY + @whereField + @criteriaSelected --+@whereField1--+ ' (('+CAST(@m_currCat AS NVARCHAR(30)) +' IS NULL ) OR (COMPANY.category = '+CAST(@m_currCat AS NVARCHAR(30)) +')) ORDER BY COMPANY.date DESC '
SET @finalQuery= @QUERY + @whereField + @criteriaSelected + ' COMPANY.active ='+CAST(@m_active AS NVARCHAR(30))+' ORDER BY COMPANY.date DESC '
EXEC(@finalQuery)
print @finalQuery
END
END
I wanted to see the result of @finalquery.how to view it?
SET @QUERY='SELECT EMAIL.email FROM companyinfo COMPANY INNER JOIN emailinfo EMAIL ON COMPANY.dataID = EMAIL.DataID LEFT OUTER JOIN category CATEGORY ON COMPANY.category=CATEGORY.ID LEFT OUTER JOIN countryinfo COUNTRY ON COMPANY.country=COUNTRY.ID LEFT OUTER JOIN regioninfo REGION ON COMPANY.region=REGION.ID '
how to see the result when there is a JOIN?
