Insert SP results into variable?

[jw56578@gmail.com]

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

[Madhivanan]

Re: Insert SP results into 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

[Simon Hayes]

Re: Insert SP results into variable?


<jw56578@gmail. com> wrote in message
news:1111709552 .442972.201630@ l41g2000cwc.goo glegroups.com.. .[color=blue]
> Is it possible to take the result from an SP that returns 1 value and
> insert it into a variable?
>[/color]

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