1

C# SqlCommand.ExecuteScalar() seems to always result in an object with value 0. Using SQL Server 2012. This is happening with all stored procedures. Here is a very simple example:

CREATE PROCEDURE [dbo].[sp_IsUnitPackaged] 
    @Serial varchar(20)
AS
BEGIN
    SET NOCOUNT ON
    SELECT COUNT(serial_number)
    FROM dbo.t_pql_contents
    WHERE serial_number = @Serial
END
GO

When I execute this directly in SSMS with a known existing value supplied for @Serial, I get the expected results:

(No column name)
1

Return Value
0

When I execute in my C# app with the following code, the result below is always 0:

string unit_serial = "something"; // The same known existing value
SqlCommand comm = new SqlCommand("sp_IsUnitPackaged");
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@Serial", SqlDbType.VarChar)
{
    Value = unit_serial,
    Direction = ParameterDirection.Input,
    Size = 20
});

int result = 0;
using (SqlConnection conn = Utils.CONN) 
{
    conn.Open();
    using (comm)
    {
        comm.Connection = conn;
        Int32.TryParse(comm.ExecuteScalar().ToString(), out result);
    }
}

I've seen some other questions asked very similar to this. The best answer I could find seemed to indicate that it is important that you not structure your stored procedure with a RETURN statement when you are expecting to use it with ExecuteScalar. As you can see, I'm not.

So, what am I missing here?

UPDATE: I've also tried adding a RETURN 0 statement to the end of my procedure so that I only have one result when executing in SSMS:

CREATE PROCEDURE [dbo].[sp_IsUnitPackaged] 
    @Serial varchar(20)
AS
BEGIN
    SET NOCOUNT ON
    SELECT COUNT(serial_number)
    FROM dbo.t_pql_contents
    WHERE serial_number = @Serial
    RETURN 0
END
GO

Executing this in SSMS gives only the following result now:

(No column name)
1

This update to the procedure continues to give a scalar result of 0 in my app, though.

Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
Solipcyst
  • 150
  • 1
  • 1
  • 11
  • Your result shows that you get two values from your query. I believe you should be adding the return statement to your procedures. Without that your procedure returns 0 as a scalar function. – MiltoxBeyond Nov 17 '15 at 17:34
  • Could it be that no row is found? Comment out the WHERE clause to make sure. – usr Nov 17 '15 at 17:38
  • 2
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Nov 17 '15 at 17:39
  • 2
    Can you verify that `TryParse` is returning `true` or `false`? – juharr Nov 17 '15 at 17:40
  • @marc_s thanks for the tip! – Solipcyst Nov 17 '15 at 18:35
  • @juharr it returns true. no exceptions. – Solipcyst Nov 17 '15 at 18:36
  • @MiltoxBeyond Please see my update in response to your comment. – Solipcyst Nov 17 '15 at 18:46
  • drop the return 0 that is where the 0 is coming from. – Daniel A. White Nov 17 '15 at 18:48
  • @Solipcyst you just repeated what you already said. This does not add anything to answer my request. Comment out the WHERE clause as requested to make sure. – usr Nov 17 '15 at 18:57
  • @usr I was trying to say that I did what you asked and a row was still found. (Only now, the value reflects the total count of all rows in that table -- as expected -- since the where clause is missing.) The ExecuteScalar result continues to be 0. This is with or without a `RETURN 0` statement in the procedure. – Solipcyst Nov 17 '15 at 19:00
  • @DanielA.White I only added the `RETURN 0` to satisfy a suggestion made by another user. My original procedure does not have it. – Solipcyst Nov 17 '15 at 19:01
  • So if you comment out the where clause and ExecuteScalar returns 0 that tells you that ExecuteScalar does not receive the result of the query at all. – usr Nov 17 '15 at 19:04
  • Actually, please fix the TryParse which has been pointed out to be broken. Use `(int)`. Update the code here. – usr Nov 17 '15 at 19:06

2 Answers2

0

Update your query as below, still if you could not see 5 as result, then there is some issue in your C# code, otherwise issue in your query.

CREATE PROCEDURE [dbo].[sp_IsUnitPackaged] 
    @Serial varchar(20)
AS
BEGIN
    SET NOCOUNT ON
    SELECT 5
    FROM dbo.t_pql_contents
END
GO
Alex Hn.
  • 81
  • 6
  • This is exactly the debugging technique that I have requested in the comments. The result is, there is something wrong with the client code :) But still, this is not an answer but a comment. – usr Nov 17 '15 at 19:49
0

Welp. As @usr pointed out in the comments (followed as well by @AlexHn.), it turned out to be a silly user error. I couldn't quite wrap my head around what they were asking me to do at first for some reason. Once I did, it became clear that I had set the value of my parameter incorrectly and so it was in fact returning an accurate result of 0.

Many apologies to all!

[I wasn't sure what etiquette dictates here on SO. As @usr pointed out, there was no real answer submitted -- only suggestions to help me find the answer myself. If anyone deserves a check, it's @usr.]

Solipcyst
  • 150
  • 1
  • 1
  • 11