You might try something like this. This is how to return variables to calling program from dynamic SQL.

set nocount on

create table foobar (id int, theid int)

insert into foobar values(1,1)

insert into foobar values(1,2)

insert into foobar values(1,2)

insert into foobar values(1,3)

insert into foobar values(1,3)

insert into foobar values(1,3)

go

create PROCEDURE myprocedure

@out_var1 varchar(5) OUTPUT,

@out_var2 varchar(5) OUTPUT,

@out_var3 varchar(5) OUTPUT

AS

BEGIN

declare @foo1 nvarchar(1000)

select @foo1 = 'select @out_var1=count(id) from foobar where theId = ''1'''

print @foo1

execute sp_executesql @foo1,N'@out_var1 varchar(5) output',@out_var1 output

declare @foo2 nvarchar(1000)

select @foo2 = 'select @out_var2=count(id) from foobar where theId = ''2'''

print @foo2

execute sp_executesql @foo2,N'@out_var2 varchar(5) output',@out_var2 output

declare @foo3 nvarchar(1000)

select @foo3 = 'select @out_var3=count(id) from foobar where theId = ''3'''

print @foo3

execute sp_executesql @foo3,N'@out_var3 varchar(5) output',@out_var3 output

return

end

go

declare @foo1 varchar(1000)

declare @foo2 varchar(1000)

declare @foo3 varchar(1000)

set @foo1='Nothing returned'

set @foo2='Nothing returned'

set @foo3='Nothing returned'

exec myprocedure @foo1 output,@foo2 output,@foo3 output

print @foo1

print @foo2

print @foo3

drop proc myprocedure

drop table foobar

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples