We help IT Professionals succeed at work.

Not getting return value from SQL Stored Procedure

Hi Experts,

I'm having trouble getting a return value back from a stored procedure.  I thought I previously had this working, and I don't understand why it's not now.  Attached is the relevant code.

Every time I run the code and step through the program I get an error in the attached database method on the following line of code:

result = command.ExecuteScalar().ToString();

Giving the error saying 'Object reference not set to an instance of an object.' - referring to the line of code trying to perform the ToString() functionality on a null object, because the ExecuteScalar() doesn't hold a value.

I know the stored procedure name and parameters I'm passing into the DB method are correct because the stored procedure is actually performing all the inserts correctly.  I just can't get it to return the value when it's done processing.

Can you see what I'm doing wrong?
/*** Stored Procedure ***/
ALTER PROCEDURE [dbo].[sp_NewProgram]
    -- Parameters:
    @SetID numeric(18,0),
    @Number int,
    @Name varchar(30),
    @Type varchar(10)
AS

DECLARE
    -- Local Variables:
    @ProgramID numeric(18,0)

BEGIN
	
    SET NOCOUNT ON;
	
    -- Insert new Program here:
    INSERT INTO [dbo].[Program]
    VALUES (@SetID, @Number, @Name, -1, -1, GETDATE(), '');

    -- Get the ID of the newly created program:
    -- SET @ProgramID = @@IDENTITY;
    SET @ProgramID =
    (
	SELECT [ID] FROM [dbo].[Program]
	WHERE [SetID] = @SetID
	AND [Number] = @Number
	AND [Name] = @Name
    );
	
    -- Insert 1 new stage with default values for the newly created program:
    INSERT INTO [dbo].[Stage]
    VALUES (@ProgramID, 1, @Type, '', 0, 0, 0, 0, 0, 'NONE', 0, 'AUTO', 0, 0, '', 0, '', '<0>', 0);
	
    RETURN @ProgramID;
	
    SET NOCOUNT OFF;
END


/*** C# Database Class Method ***/
public static String ExecuteScalar(String StoredProcedureName, SqlParameter[] Parameters)
{
    string result = string.Empty;
    try
    {
        connnection = new SqlConnection(connString);
        command = new SqlCommand(StoredProcedureName, connnection);
        command.CommandType = CommandType.StoredProcedure;

        foreach (SqlParameter parameter in Parameters)
            command.Parameters.Add(parameter);

        command.Connection.Open();
        result = command.ExecuteScalar().ToString();
    }
    catch (Exception ex)
    {
        string error = ex.ToString();
    }
    finally
    {
        command.Connection.Close();
        command.Dispose();
        connnection.Dispose();
    }
    return result;
}

Open in new window

Comment
Watch Question

Commented:
I thought that procedures do not return any value, and that you will need to create a function in order to return a value?
Commented:
hi
replace RETURN @ProgramID;
with
select @ProgramID;

and check
I think @ProgramID must be declared inside the procedure declaration as OUTPUT, implicit.

ALTER PROCEDURE [dbo].[sp_NewProgram]
    -- Parameters:
    @SetID numeric(18,0),
    @Number int,
    @Name varchar(30),
    @Type varchar(10),
    @ProgramID int OUTPUT
AS

Commented:
Hi P1ST0LPETE,

Remove  SET NOCOUNT ON; in the procedure, becuase by letting this stmt on, you are telling your procedure to not remember any update/insert count. So your ExecuteScalar(...) has been returning null only.

Give a shot and let us know.

Happy Programming,
Aksh

Author

Commented:
Ok, thanks for the replys guys.

@royend - Stored Procedures can absolutely return values

@cesardanielleon - You would be correct if I was passing in the ProgramID parameter, but since I am not passing it in, I can just create and use a local variable like i'm doing.

@Aksh - Tried your method, didn't work.  Why would telling the procedure not to remember the update/insert count clear out the value held in ProgramID ?

@pbehin - You are correct my friend.  After I posted the question, I was beginning to think my issue was due to the fact that I was using a ExecuteScalar() method which would be looking for a SQL Select result, which I was not providing.  So you answer definitely turned on the light.
this
select @ProgramID;


will not work on some scenarios, like JDBC or ODBC.