Azure Stored Procedure does not return inserted ID to ASP.NET App

Question

I have a stored procedure defined on Azure SQL Database Server.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertGameSession]
    (@finished BIT,
     @createUser VARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO [dbo].[game_Session]
           ([finished]
           ,[createUser]
           ,[createDate]
           ,[changeUser]
           ,[changeDate])
     VALUES
           (@finished
           ,@createUser
           ,CURRENT_TIMESTAMP
           ,@createUser
           ,CURRENT_TIMESTAMP)
     RETURN SCOPE_IDENTITY();
END

Called from ASP.NET Application through a data adapter.

Command Type : Stored Procedure
Execution Mode : NonQuery
Modifier : Public
Parameters :
- @RETURN_VALUE, ColumnName = id, Direction = ReturnValue, SourceColumn = id
- @finished, ColumnName = finished, Direction = Input, SourceColumn = finished
- @createUser, ColumnName = createUser, Direction = Input, SourceColumn = createUser

If the stored procedure is called from DBMS, a correct id is being returned.

If the stored procedure is called from ASP.NET web application, return value is suddenly 0 or null.

What should I do to get the same result in ASP.NET?

Since your stored procedure is **returning** something (the newly inserted Id), you must call is using `ExecuteScalar` - not `ExecuteNonQuery` .... — marc_s, Nov 24 '18 at 20:40
@marc_s thank you for your suggestion. So I changed it to `ExecuteScalar` but now my return value is an Object which is always null. Do you have any suggestion? — Hugo Vrana, Nov 24 '18 at 20:54
Parameter direction ReturnValue is unrelated to whether you use `ExecuteScalar` or `ExecuteNonQuery` because it is not part of the result set. Show your code. Also consider [using `OUTPUT`](https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) in the query instead of `RETURN` which is meant to communicate status, not data. — Crowcoder, Nov 24 '18 at 21:54

score 0 · Answer 1 · answered Nov 24 '18 at 23:56

Not sure without seeing the calling code, but you should never return data using the return value of a stored procedure. That's intended to indicate success or failure, and only when called from TSQL. Instead return data using an output parameter or a resultset. So

CREATE OR ALTER PROCEDURE [dbo].[InsertGameSession]
    (@finished BIT,
     @createUser VARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO [dbo].[game_Session]
           ([finished]
           ,[createUser]
           ,[createDate]
           ,[changeUser]
           ,[changeDate])
     VALUES
           (@finished
           ,@createUser
           ,CURRENT_TIMESTAMP
           ,@createUser
           ,CURRENT_TIMESTAMP)
     SELECT SCOPE_IDENTITY() Id;
END

And then from client code retrieve the new ID with ExecuteScalar(), or ExecuteReader().

Azure Stored Procedure does not return inserted ID to ASP.NET App

1 Answers1