set @var = exec stored_procedure

Question

Is it possible to assign at variable a value returned from exec stored procedure?

Something like

DECLARE @count int
SET @count = Execute dbo.usp_GetCount @Id=123

Answer 1

If you use RETURN in the proc

DECLARE @count int
EXECUTE @count = dbo.usp_GetCount @Id=123

OUTPUT parameter

DECLARE @count int
EXECUTE dbo.usp_GetCount @Id=123, @count OUTPUT

Redirect the results to temp table/table variable

DECLARE @count int
DECLARE @cache TABLE (CountCol int NOT NULL)
INSERT @cache EXECUTE dbo.usp_GetCount @Id=123
SELECT @count = CountCol FROM @cache

You can't assign a recordset from the stored proc directly to a scalar variable

Answer 2

You can use sp_executesql instead of exec to assign to scalar output parameters

DECLARE @out int

EXEC sp_executesql N'select @out_param=10',
                   N'@out_param int OUTPUT',
                     @out_param=@out OUTPUT

SELECT @out

For exec I'm only aware of how to do it using a table variable

declare @out table
(
out int
)

insert into @out
exec('select 10')

select * 
from @out

For stored procedures you would also use an output parameter or a return code. The latter can return a single integer only and generally preferred for returning error codes rather than data. Both techniques are demonstrated below.

create proc #foo 
@out int output
as
set @out = 100
return 99

go

declare @out int, @return int

exec @return = #foo @out output

select @return as [@return], @out as [@out]

drop proc #foo

Answer 3

As usual many ways to do this but the easiest is:

DECLARE @count int

Execute @count =  dbo.usp_GetCount @Id=123