printing dynamic SQL in stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

printing dynamic SQL in stored proc

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2010-04-12 : 19:08:21

Can anyone see the reason why when I run this proc from within the management studio, it will NOT print and it won't give me results in the management studio. I get results when running it from the .NET app. But I want to print it out to debug. Thanks!

USE [leadgen]
GO
/****** Object:  StoredProcedure [dbo].[sp_search_prod_serv]    Script Date: 04/12/2010 16:26:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_search_prod_serv]
(
   @search_text VARCHAR(200),
   @flag		CHAR(1),
   @country     VARCHAR(20),
   @state       VARCHAR(20),
   @date_range	VARCHAR(20),
   @account_id	VARCHAR(100),
   @user_id varchar(150) = null,
   @affiliates bit = null   
)
AS
BEGIN

DECLARE
@qry	VARCHAR(8000),
@qry_country	VARCHAR(100),
@qry_state		VARCHAR(100),
@qry_account_id	VARCHAR(200),
@gap			VARCHAR(100),
@qry_sic		VARCHAR(100),
@qry_gap		VARCHAR(200),
@qry_aff		VARCHAR(500)

IF @affiliates = 1 
	BEGIN
		select @qry_aff =  ' and (account_id IN (SELECT tbl_affliation.account_id
														FROM tbl_affliation 
															INNER JOIN tbl_user_information ON tbl_affliation.user_id = tbl_user_information.user_id
																WHERE Substring(promo_code,6,2) = (SELECT Substring(promo_code,6,2) FROM tbl_user_information WHERE user_id=''' + @user_id + ''')))'
	END
ELSE
	BEGIN 
		select @qry_aff = ''
	END 

if @search_text is null 
	select @qry_sic = 'sic_code is not null'

if @flag='C'
	select @qry_sic= ' sic_code in '+'('''+@search_text+''')'

if @flag='S'
	select @qry_sic= ' description like '+'''%'+@search_text+'%'''

if @state <> 'All'
		select @qry_state= 'State ='+''''+@state+''''
else
		select @qry_state = 'State is not null'

if @country <> 'All' 
		select @qry_country= 'Country='+''''+@country+''''
else
		begin
			select @qry_country= 'Country is not null'
			--select @qry_state = 'State is not null'
		end

if @account_id is not null
		select @qry_account_id = ' account_id <>'+''''+@account_id+''''
else
		select @qry_account_id = ' account_id is not null'

if @date_range='Last Week'
	select @qry_gap=' posted_date between getdate()-7 and getdate()'


if @date_range='Last Month'
	select @qry_gap=' posted_date between getdate()-30 and getdate()'


if @date_range='Last 2 Months'
	select @qry_gap=' posted_date between getdate()-60 and getdate()'


if @date_range='Last Year'
	select @qry_gap=' posted_date between getdate()-365 and getdate()'

if @date_range='All Dates'
	select @qry_gap=' posted_date is not null'

select @qry=
'select * from view_prod_serv where ' + @qry_sic + ' and ' +@qry_state+' and '+ @qry_country+' and '+@qry_account_id+ 
' and '+ @qry_gap + @qry_aff

print @qry
--exec (@qry)

end