C# Getting Return Value from Stored Procedure Is Not Working

Question

I am trying to run a stored procedure returning a single integer value and I can't figure out where its going wrong. From what I've read the following should work but is not and I can't see where I'm going wrong.

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 try 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 the line giving the error is:

            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 to return completion codes to the calling function. As such, the values available to be returned are limited. What you need to do instead is to SELECT @tempID and treat it as a result set.