Retrieving values from dynamic SQL

Anyone know if it's possible to retrieve a parameterized value from dynamically executed SQL?

Code Example

Declare @.table varchar(25)
Declare @.somevalue varchar(3)
Set @.table = 'sometable'
Set @.somevalue = 'somevalue'
Declare @.sqlBuild varchar(2000)
Set @.sqlBuild = 'DECLARE @.return varchar(3); ' +
 ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
exec (@.sqlBuild)

i want to be able to extract the value of @.return for later use. this procdure works fine but I need to grab that value somehow.

Any suggestions?put a the end of your stored procedure select @.return ...
Use a ExecuteScalar in your code and it will be returned ...|||i'm not quite sure what you mean...

can you give me an example?|||You will need to use sp_executesql in order to capture the OUTPUT parameter from a dynamic SQL statement. Something like this (note that your @.sqlBuild needs to be of type nvarchar):

DECLARE @.table varchar(25), @.somevalue varchar(3), @.return integer, @.sqlBuild nvarchar(4000)
SELECT @.table = 'sometable', @.somevalue = 'somevalue'
SELECT @.sqlBuild = ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
EXEC sp_executesql @.sqlBuild, N'@.return integer OUTPUT', @.return OUTPUT

Terrisql