Print output of dynamic query in SQL Server

Last Reply one year ago By pandeyism

Posted one year ago

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?