ExecuteScalar returns 0 executing a stored procedure

Question

I'm refactoring a C# program that calls a stored procedure that ends with:

SELECT @ResultCode AS ResultCode

The C# code looks like this:

SqlDbCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlDbCommand.CommandText = "PR_Foo";

SqlDbCommand.Parameters.Clear();
SqlDbCommand.Parameters.Add("@Foo", SqlDbType.Char);  
SqlDbCommand.Parameters["@Foo"].Value = 'Foo';

System.Data.SqlClient.SqlDataAdapter SqlDbAdapter = new System.Data.SqlClient.SqlDataAdapter();
System.Data.DataSet SQLDataSet = new System.Data.DataSet();
SqlDbAdapter.SelectCommand = SqlDbCommand;
SqlDbAdapter.Fill(SQLDataSet);
SQLDataSet.Tables[0].TableName = "PR_Foo";

if (SQLDataSet.Tables.Count != 0) {
       Result = int.Parse(SQLDataSet.Tables[SQLDataSet.Tables.Count - 1].Rows[0][0].ToString());
}

With the above code, Result is correctly populated with the value returned by the
stored procedure.

Refactoring the code with a simpler ExecuteScalar:

SqlDbCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlDbCommand.CommandText = "PR_Foo";

SqlDbCommand.Parameters.Clear();
SqlDbCommand.Parameters.Add("@Foo", SqlDbType.Char); 
SqlDbCommand.Parameters["@Foo"].Value = 'Foo';

Result = (int)SqlDbCommand.ExecuteScalar();

the Result value is oddly set to 0 while the expected result should be an integer value greater than zero.

Do you know what could be the cause of this strange behavior?

Note:

the stored procedure has several if blocks, returning result values lower than zero in case of particular checks; these cases are correctly handled by the ExecuteScalar().

The problem raises when the stored procedure does its job correctly, committing the transactions of the various updates and returning the Result value at the end.

Is `SET NOCOUNT ON` in your stored procedure? Where is the SQL that deals with `@Foo`? — Jodrell, Aug 09 '11 at 14:28

score 4 · Answer 1 · answered Oct 31 '12 at 12:06

I also encountered this problem. In my opinion it is very relevant. So I decided to give here the correct sample of code.

    SqlCommand cmd2 = new SqlCommand();
    cmd2.Connection = conn;
    cmd2.CommandType = System.Data.CommandType.StoredProcedure;
    cmd2.CommandText = "dbo.Number_Of_Correct";

    SqlParameter sp0 = new SqlParameter("@Return_Value", System.Data.SqlDbType.SmallInt);
    sp0.Direction = System.Data.ParameterDirection.ReturnValue;
    SqlParameter sp1 = new SqlParameter("@QuestionID", System.Data.SqlDbType.SmallInt);

    cmd2.Parameters.Add(sp0);
    cmd2.Parameters.Add(sp1);

    sp1.Value = 3;

    cmd2.ExecuteScalar();     // int Result = (int)cmd2.ExecuteScalar();  trowns System.NullReferenceException
    MessageBox.Show(sp0.Value.ToString());

score 4 · Accepted Answer · answered Aug 09 '11 at 14:06

4

In the event of multiple tables being returned your two piees of code aren't doing the same thing. Your original code takes the first field of the first row of the last table whereas the execute scalar will take the first field of the first row of the first table. Could this be where your problem lies?

answered Aug 09 '11 at 14:06

Chris

27,210
6
71
92

but, as I said, the StoredProcedure returns a single value. – systempuntoout Aug 09 '11 at 14:19
@systempuntout: In all fairness you just said that it ended with `SELECT @Resultcode`. That doesn't mean that there are no other SELECTS elsewhere that could be returning that 0 before the end. The fact that the original bothers to explicitly get the last table rather than relying on the knowledge there is only one select statement returning anything led me to believe that this was a likely cause. Is the procedure pasteable or is it too big/too secret? – Chris Aug 09 '11 at 14:29
you are right, I've run SQL Profiler and there are more rows from different tables returned, I have not thought about that. Thanks :) – systempuntoout Aug 09 '11 at 14:31
@systempuntoout: Glad to have helped. I almost didn't follow up since I was worried I was patronising a 15K user who would know better than this. Glad I did though. ;-) – Chris Aug 09 '11 at 14:39
A 15K out of his comfort zone :) – systempuntoout Aug 09 '11 at 15:19

canon · Answer 3 · 2011-08-09T14:38:32.733

What you seem to want is the first column of the first row of the last result-set. Unfortunately, if you have multiple select statements in your procedure, thereby generating more than one result-set, an ExecuteScalar is only going to get the first column of the first row of the first result-set.

Your first block of C# code checks the last table in the dataset which would (correctly) be associated with the last select statement in the procedure.

score 2 · Answer 4 · answered Aug 09 '11 at 14:24

2

If you have multiple potential result sets, and there is potential for ambiguity you might consider changing the stored procedure to use an output parameter instead of a select on @ResultCode.

answered Aug 09 '11 at 14:24

cmsjr

56,771
11
70
62

thanks for the hint, I have proposed this loong time ago but there are hundreds of SP to refactor like this. – systempuntoout Aug 09 '11 at 14:34
np, best of luck with your mass refactor. – cmsjr Aug 09 '11 at 14:37

ExecuteScalar returns 0 executing a stored procedure

4 Answers4