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

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

<jw56578@gmail.com> wrote in message
news:1111709552.442972.201630@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