accessing variable outside of exec stmt 1

[sagn]

Hi,

How can I access the value of a variable outside of
an exec statement.
I have tried the following

1:
declare @i int
set @i = exec ('select count(*) from table')

of course this does not work and I understand why
but I thought I would try anyway.

2:
exec ('declare @row int
set @row = (select count(*) from table)')

@row is not recognized outside of the exec stmt.

Declaring @row before the exec stmt does not work either

I want to be able to do this since I need to use an exec statement because the table name is actually a variable
(@tablename) and I want to use @row variable
elsewhere...

Thanks

ds

 

[tlbroadbent]

Instead of using EXECute to execute the SQL, use sp_executesql. It allows you to use output parameters.

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

See the following article for more info.

INF: Using Output Parameters with sp_executesql

http://support.microsoft.com/support/kb/articles/Q262/4/99.ASP

Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln