Query Output in C# differs from output everywhere else

I have a stored procedure that returns a calculated field. The query looks kinda simliar to the following:

SELECT
    ISNULL(udf.SomeID, t.SomeID - 1) - t.SomeID + 1 AS IsHappy
    -- SomeID is an integer greater than 0
    -- t.SomeID will always exist
    -- udf.SomeID is either NULL or == to t.SomeID
    -- IsHappy should == 0 if udf.SomeID == NULL and 1 otherwise
FROM
    SomeUDF() AS udf,
    SomeTable AS t

This sp has been used for months by various applications without a hitch. When I run the sp I receive a result set where IsHappy is either 0 or 1 and everything looks great.

When I run this very same sp in a shiny new C# application, however, the results are different. Some records which are supposed to have IsHappy == 0 instead == 1. Eh !

I have tried manipulating the result in all sorts of different ways including doing things like:

    (int)reader["IsHappy"]
    Convert.ToInt32(reader["IsHappy"])
    Int.Parse(reader["IsHappy"].ToString())

And they all return the same incorrect results: IsHappy == 1 when it should be 0.

And, just for the record, whether or not the query can be written better doesn't really matter. :) What bothers me is that the results from the C# execution of the query differ than the results received everywhere else.

Any ideas

Cheers!
Andrew Penniman

Answer this question

Query Output in C# differs from output everywhere else

itzik
Have you tried doing a trace on the database
Rongping
As Sherlock Holmes said, "Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth.”

I would say that it is just about impossible for a call to an SP on the same database with the same parameters to be giving different results just because it is being called from C#.

So you will need to look elsewhere. Are you sure you are connecting to the same db Are you sure you are passing the same parameters Are you sure you are interpreting the query results correctly
高?
I agree - you should be using SQL Profiler to find out what the difference is when you call it from C# and when you don't.

I have seen things like different language settings on the login giving different results.
FETUS

My bet is that it's some value lost when converting between the following: boolean/DB Boolean/Integer (either signed or unsigned) - something to do with logic such as (if boolVal != 1, then false, when boolVal actually equals -1... which should be true.)

First have a look at your DB trace, and confirm that the output of the stored procedure is the same, given the same input parameters, then check that your boolean value isn't fudged/misrepresented/inadvertently changed when trying to convert between different datatypes.
devmonkey
Aye, I have confirmed all these things. Same database, same parameters, same stored procedure. I even tried running the query directly as a SELECT statement instead of a stored procedure with the same results.

I have tried executing both the raw SELECT statement as well as the stored proc in multiple applications using multiple languages, including VB6, ColdFusion, PHP, and SQL's Query Analyzer, and they all work as expected. Only C# gives me trouble.

I am at a complete loss!

For my next step I think I am going to pre-calculate IsHappy and populate a temporary table with the results. I will then JOIN the temp table with the original query and see what happens. *shrug*
Query Output in C# differs from output everywhere else

Answer this question