sp_executesql in SSMS2008 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sp_executesql in SSMS2008

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-04 : 13:27:55

hi

i'm running a simple

execute sp_executesql @sql
in SSMS 2008
but i don't get results in result tab.

is there any way to enable it to see the output of this procedure.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:30:02

what have you set as value for @sql? the output depends on query build using @sql string so you may try running the query build based on value passed directly.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-04 : 13:56:23

same query was performing great in SSMS 2005, but 2008 isn't working at all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:58:03

whats was the query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-04 : 14:01:15

sample code:


create table AB
(te_xt varchar(20)
,te_xt2 varchar(20)
,ID int identity(1,1)
)

insert into AB (te_xt, te_xt2) values ('lala1','lala2')
insert into AB (te_xt, te_xt2) values ('lala3','lala4')
insert into AB (te_xt, te_xt2) values ('lala5','lala6')

select * from AB

declare @sql nvarchar(1000)
declare @table varchar(100)
declare @column varchar(100)
declare @id nvarchar(100)
declare @id_out nvarchar(100)

set @table = 'AB'
set @column = 'te_xt'

set @sql = N'select ' + @column + N' from ' +  @table + N' where ID = ' + @id
print @sql
exec sp_executesql @sql, @id = @id_out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 14:05:19

you're not initialising the @id variable or @id_out so it will have NULL value. thats why your query doesnt return anything as = operator will not work with NULL values under default conditions. May be your 2005 server had different ANSI NULLS setting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-04 : 14:09:24

visakh,

even if i comment the last row of code


--exec sp_executesql @sql, @id = @id_out

print is not working.

i've tried SET ANSI_NULLS ON|OFF and no change at all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 14:12:54

print wont work unless you change CONCAT NULL YIELDS NULL setting as in @sql you're concatenating string value to NULL.

anyways try this small change without changing any setting


set @sql = N'select ' + @column + N' from ' +  @table + N' where ID = ' + COALESCE(@id,'')
print @sql

and see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources