CODEWITHSUNDEEP
c#sql-serverstored-procedures
james33
james33

Reputation: 109

C# Getting Return Value from Stored Procedure Is Not Working

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

Upvotes: 5

Views: 4628

Answers (2)

HABO
HABO

Reputation: 15852

ExecuteScalar returns the value of the first column of the first row of the 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 receive the return value from the stored procedure.

Please note that the return value is intended only for returning a status. You should use an OUTPUT parameter to return @TempId.

Upvotes: 2

Ellesedil
Ellesedil

Reputation: 1626

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.

Upvotes: 3

Related Questions