c# - Get value output from SQL Server stored procedure into variable

Question

This seems to be a simple question but nevertheless I haven't found an answer yet.

I have the following stored procedure

CREATE PROCEDURE [dbo].[AllocateId]
AS
  BEGIN TRANSACTION

  UPDATE TOP(1) IdReservation 
  SET IsAllocated = 1
  OUTPUT DELETED.Id
  WHERE IsAllocated = 0 

  COMMIT TRANSACTION
  GO

It's been used in C# + EF code without a problem via ExecuteFunction of ObjectContext

ObjectResult<int> objectResult = ExecuteFunction<int>("AllocateId");

However when I try to call it directly from SQL script it doesn't seem to work

declare @Id int
EXEC @Id = [dbo].[AllocateId]

@Id is always 0. How do I get the value into @Id variable in sql script?

Answer 1

Procedure return value is different from result set(s) returned by that procedure. Your stored procedure returns a result set and does not return a return value (which ends up being null, which gets converted to 0 implicitly upon exiting the procedure).
To get the resultset your existing procedure retuns, you need insert ... exec:

declare @t table (id int);

insert into @t
exec [dbo].[AllocateId];

If you want to return a value as a return value as well, you should amend you stored procedure:

CREATE PROCEDURE [dbo].[AllocateId]
AS
BEGIN TRANSACTION

declare @id int;

UPDATE TOP(1) IdReservation
    SET @id = Id, IsAllocated = 1
    OUTPUT DELETED.Id
    WHERE IsAllocated = 0 

COMMIT TRANSACTION

return @id;

Then it will work in the way you describe in the question.