how to store output of exec stmt in another variable?

Hi

I am trying to store the output/result of exec statement in another variable like @.b.

Code Snippet

declare @.a varchar(10)

declare @.b int

set @.a='2 * 3';

EXEC ('select ' + @.a)

in the above sample I need to store 6 in @.b, how?

Please advice

Thanks

You could use sp_executesql like:

declare @.res int /*?*/;

declare @.sql nvarchar(max);

set @.sql = N'SELECT @.res = ' + @.a; -- Protect against SQL injection by using QUOTENAME as appropriate etc...

exec sp_executesql @.sql, N'@.res int OUTPUT', @.res = @.res OUTPUT;

But why do you want to do this? What are you trying to achieve? In SQL Server 2005, you could store the formula in a table and evaluate using CLR logic. This will actually perform better for complex calculations and will not suffer from SQL Injection issues. Otherwise, you should try to avoid dynamic SQL as far as possible. It is easy to code but hard to get it right in terms of security, performance and manageability. The risks are too much if you miss anything.

|||

Thanks a lot Chandar, for your nice reply

I got my answer from your query

The reason why I want this one is

I calculated the product of some values in a Table's column using COALESCE & EXEC Statements. But I am unable to store that result in a variable.

But now I got it using exec sp_executesql etc…..

Once again Thanks

|||You don't need to use dynamic SQL to get the product of values. You can do that with simple expressions using SUM and LOG. Search in this forum for my posts on this topic - I have some links that point to sample code that you can use.