CODEWITHSUNDEEP
sqlsql-server-2008
Red Devil
Red Devil

Reputation: 2393

How to set a variable which include another variable in dynamic sql

Here is my Query:-

declare @a nvarchar (150)
declare @b nvarchar (100)
set @b= 'Test'
set @a =('select COUNT(*) from ' +@b + '.dbo.t_ddl_log')
exec (@a)
if (@a='0')
print 'True'
else
print 'False'

It is always printing False even though it should print True.
If I change the variable @b with Test in my count query it works fine.
What is the problem in the query??

Upvotes: 0

Views: 80

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

The problem is not with the query - the problem is that you mistakenly think that @a should hold the results of the query, while in fact it holds the query itself.

You can use sp_executeSql to get the results you want:

declare @a nvarchar (150),
        @b nvarchar (100),
        @c int
set @b= 'Test'
set @a =('select @count = COUNT(*) from ' +@b + '.dbo.t_ddl_log')

exec sp_executeSql @a, N'@count int output', @c = @count output

if (@c= 0)
    print 'True'
else
    print 'False'

Upvotes: 1

Related Questions