How to capture return value from 'execute'

declare @.table varchar(100);

declare @.q varchar(100);

declare @.key bigint;

select @.table = 'key_table';

select @.q = 'select key from ' + @.table;

select @.key = exec(@.q); -> not working.

Check in books online; you'll find that the string execute version of the EXEC command does not provide the same ability to capture a return value as does the execution of a stored procedure. Sorry.|||

In that case, can you suggest an alternative for the my requirement. I want to get the return value of a select statement constructed dynamically.

Thanks,

|||

create a temp table and use this type

insert into #tempTable

exec ( @.yourExecString )

Also, understand that using the EXEC ( @.yourExecString ) syntax might leave you subject to SQL INJECTION attacks.

|||

Use sp_executesql instead EXEC(...). You can use output paraeters with this sp.

declare @.table sysname;

declare @.q nvarchar(100);

declare @.key bigint;

select @.table = N'key_table';

select @.q = 'select @.key = key from dbo.[' + @.table + N']';

exec sp_executesql @.q, N'@.key bigint output', @.key output;

select @.key

go

Be careful with sql injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB