How to get variable's value?

[desaivar]

Hi there,

I am constructing one sql statement in variable and want to execute the sql statement.

What I want to achieve is, I want to store value of testID of second record in one vaiable called @testID.

Following is the code,

declare @strQuery varchar(4000)
declare @testID varchar(6)

set ROWCOUNT 2

Set @strQuery ='SELECT @testID=TestID from dbo.vwBLT_SearchResultsAllDataTest'

exec(@strQuery)

print @testID

But it gives me following error,

Must declare the variable '@testID'.

The fact is if I don't store sql statement in @strQuery variable and directly execute sql statement such as below it gives me @testID value.

declare @testID varchar(6)
set ROWCOUNT 2
select @testID=TestID from dbo.vwBLT_SearchResultsAllDataTest
print @testID

but I want it in first way because I am constructing 'where' condition based on various criteria's which would be attached to above select sql statement so I need to keep whole sql statement in variable.

Is there any way I can get value for @testID by keeping whole sql statement in @strQuery variable or any other way to achieve solution?

I am struggling with this issue since from so many days and now it is urgent. Your help would be really appreciated.

 

[markros]

Yes, there is a way by using sp_executeSQL.

Check Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

and
Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly

 

[TysonLPrice]

I changed the table name to something I have but this seems to work (put the declare in the string)

declare @strQuery varchar(4000)

set @strquery = 'declare @testID int set ROWCOUNT 2 
select @testID=pkClaim  from claim
print convert(varchar,@testid)'

exec(@strQuery)

 

[ddiamond]

declare @strQuery nvarchar(4000)
declare @testID varchar(6)

set ROWCOUNT 2

Set @strQuery =N'SELECT @testIDOut=TestID from dbo.vwBLT_SearchResultsAllDataTest'

sp_executesql @strQuery, N'@testIDOut VARCHAR(6) OUTPUT', @testIDOut = @testID OUTPUT

print @testID

 

[markros]

Just a link giving the same solution as ddiamond

http://forum.lessthandot.com/viewtopic.php?f=17&t=9240

 

[desaivar]

It is working with the help of sp_executesql statement.

Thanks for your help