How to get variable's value? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to get variable's value?

Author

Topic

desaiva
Starting Member

2 Posts

Posted - 2009-12-26 : 08:15:47

Hi there,

I am constructing one sql statement in variable and want to execute this 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.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-26 : 08:34:22

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

set ROWCOUNT 2

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

EXEC sp_executesql
@query = @strQuery,
@params = N'@testID varchar(6) OUTPUT',
@testID = @testID OUTPUT

PRINT @testID

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-29 : 01:58:45

OR

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

set ROWCOUNT 2

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

exec(@strQuery)

print @testID

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-29 : 03:25:16

quote:
Originally posted by senthil_nagore

OR

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

set ROWCOUNT 2

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

exec(@strQuery)

print @testID

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Why are you concatenating @testID which is not at all initialised to any value?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources