dynamic sql 1

[bunmiA]

I'm trying to display the following
'No entry',
'No exit'
in the result set of a sql query (on two seperate lines)... using dynamic sql. could you please spot what I am doing wrong.. I cannot seem to get the quotes right!!

here is the code

declare @scomm1 char

set @scomm1 =
' print ' + '''' + 'No entry' + '''' + ' + ' + ','
' print ' + '''' + 'No exit' + ''''

execute (@scomm1)

 

[Pattycake245]

This will work:

select @scomm1 = N'print '+ '''no entry''' +
'print '+ '''no exit'''
exec sp_executesql @scomm1

Tim

 

[NoCoolHandle]

Try
declare @scomm1 varchar(3000)
print @scomm1
set @scomm1 = 'print ''''''No entry'''','
+ char(10) + '''''No Exit'''''''

execute (@scomm1)

A couple of issues "declare @variable char" = 1 character..
i.e.

declare @x char
set @x = 'this is some text'
print @x

Returns 't'

And you were executing two print commands.. I am not sure of the value of this, I just found it easier to return a single two line message. Two print statements would require you itterate throught the infomessages collection. A pain to impossible (depending on client/provider)

 

[bunmiA]

thanks....

I ran this as you said.... it says the query completed successfully... but it doesnt print the statement..... ?<>?

declare @scomm1 nvarchar

select @scomm1 = N' print '+ '''no entry''' +
' print '+ '''no exit'''
exec sp_executesql @scomm1

 

[Pattycake245]

I missed a couple of things from your question, NoCoolHandle's solution works fine

Tim

 

[bunmiA]

thanks nocoolhandle

this solution is fine... except for a minor detail....

I intend to print the values of a table... I have place this in a cursor ant it works fine... the only thing is... how do I know what value to set for varchar(??????)

 

[NoCoolHandle]

Bottom line go all 8000 possible spaces.. that way it will use up to 8000 char. If you only need 3 it only uses 3.


Rob
PS
If you are wanting to output to a recordset use a select not a print.

I.E
declare @scomm1 varchar(3000)
print @scomm1
set @scomm1 = 'Select ''''''No entry'''','' union Select ''''''No Exit'''''''
execute (@scomm1)

 

[SQLBill]

You could run:

SELECT MAX(your_column_name)
FROM your_table_name

That will give you the length of the longest row from the specified column. Then use that value for the VARCHAR length.

I don't know if you can do something like this or not:

DECLARE @myvar INT
SET @myvar = (SELECT MAX(your_column_name)
FROM your_table_name)

DECLARE @scomm1 VARCHAR(@myvar)
<rest of code>

-SQLBill

 

[bunmiA]

Thanks Pattycake....

I deceided to go for your solution as it will help me to achieve what I need best.

However Could I add a declared variable in as below??

declare @scomm1 nvarchar(90),
@set char (20)

set @set = 'showme'
select @scomm1 = N'print '+ '''no entry'' + '','' + ''no exit'' + '',''' + @set
exec sp_executesql @scomm1

 

[Pattycake245]

Yes you can, but the quotes are hard to figure out, but I finally got the quotes right:

declare @scomm1 nvarchar(90), @set nvarchar(100)

select @set = 'show me'
select @scomm1 = N'print ''''''no entry'''',''''no exit'''','''''+@set+''''''''
exec sp_executesql @scomm1

Tim

 

[bunmiA]

thanks Tim...

I have used the code below as I dont need the quotes.... however.. I'm still having trouble displaying @set!!!!!!...
any ideas.. (I've commented @set out so that the query works)

declare @scomm1 nvarchar(100),
@set nvarchar(80)

select @set = 'show'
select @scomm1 = N' print ''no entry'' + '','' + ''no exit''+ '','''
-- + @set

exec sp_executesql @scomm1

 

[Pattycake245]

This prints out:

no entry,no exit,show

declare @scomm1 nvarchar(100),
@set nvarchar(80)

select @set = 'show'
select @scomm1 = N' print ''no entry'' + '','' + ''no exit''+ '',''+ '''+@set+''''

exec sp_executesql @scomm1

Tim

 

[bunmiA]

Thanks Tim...