Insert SP results into variable?

[jw56578]

Is it possible to take the result from an SP that returns 1 value and
insert it into a variable?

[Madhivanan]

DECLARE @x varchar(9), @sql1 nvarchar(200)
SELECT @sql1=N'select @x=count(*) from tt'
EXEC sp_executesql @sql1, N'@x varchar(9) OUTPUT', @x output
Select @x

Madhivanan

[Simon Hayes]

<jw*****@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Is it possible to take the result from an SP that returns 1 value and
insert it into a variable?

If a procedure returns one value, then you should use an output variable:

create proc dbo.p_out
@i int output
as
set @i = 1
go

declare @num int
exec dbo.p_out @i = @num output
select @num

This is usually the best approach for procedures which return only one row
of data:

http://www.sommarskog.se/share_data.html#OUTPUT

Simon