C # Getting return value from stored procedure does not work

Question

I am trying to start a stored procedure by returning a single integer value, and I cannot figure out where this is happening. From what I read, the following should work, but it is not, and I do not see where I am mistaken.

Here is my stored procedure:

ALTER PROCEDURE [dbo].[getDDNTempID] AS BEGIN declare @tempID int select top 1 @tempID = tempID from tblDDNHdr order by tempID asc if @tempID is null return 0 else return @tempID END 

Here is the code where I am trying to get the return value:

  using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString)) { SqlCommand cmd = new SqlCommand("getDDNTempID", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.Open(); Int32 tempID = (Int32)cmd.ExecuteScalar(); if (tempID == 0) { tempID = -1; } return tempID; } 

When this procedure is called, I get a NullReferenceException, and a line indicating an error:

  Int32 tempID = (Int32)cmd.ExecuteScalar(); 

I would appreciate any guidance you guys could give.

thanks

Answer 1

The return function in SQL Server is specifically designed to return the exit codes of the calling function. Therefore, the values ​​available for return are limited. Instead, do SELECT @tempID and treat it as a result set.

Answer 2

ExecuteScalar returns the value of the first column of the first row of results. Your stored procedure does not return a result set.

Add a parameter to the SqlCommand.Parameters collection and set the Direction to ReturnValue . It will get the return value from the stored procedure.

Please note that the return value is for status return only. You must use the OUTPUT parameter to return @TempId .