return question 1

[sagn]

I have a stored procedure that uses an exec statement.
I use the exec statement because I want to
get info from a table
which is known through a parameter.

for example I have:
/* begin SQL */
create [sp]

@tablename varchar(50)

AS
exec ('
declare @err int
set @err =
(select count(*) from '+@tablename+' where a=1)
')

But I want to be able to return @err. Unfortunately
I can;t seem to do that from within the EXEC and
@err of course is out of scope outside of EXEC.

How can I accomplish this or something equivalent?

Thanks yet again

ds

 

[tlbroadbent]

You can use sp_executesql to return values. See SQL BOL for complete info and syntax. Here is an example.

declare @str nvarchar(500), @err int
set @str = N'select @err=count(*) from '+@tablename+' where a=1'
execute sp_executesql @str, N'@err int out', @err out
select @err Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[sagn]

Totally cool. I'll give it a try!


ds