Different result in SP vs Query Analyzer

I'm getting some very odd behaviour which I can currently
duplicate. One of my SPs fails when it can't find a
record. I copy the SQL from the SP and execute it via
query analyzer and ta-da, the record appears.
Further investigation shows that the sql checks that a
record doesn't have a "completed" status. Comment this
out and the SP works fine.
Continuing the investigation, the developers run this SP
through debug and find the value being returned
= 'COMPLETED'. Run the *same* code in query analyzer and
the value being returned is 'PLANNED'... How is this
possible' The sql returns the correct value when run
through query analyzer, but the incorrect value when run
as a part of the SP!!
Below is the 'where' clause:
.
.
.
Where
(vtt.Trip_Id = @.FCTripId)
And (eqm.eqm_sequence_no = 2)
And (cst.cst_consign_status_desc <> 'COMPLETED')
TIA,
SJT> I'm getting some very odd behaviour which I can currently
> duplicate.
Can you give us enough information so we can try to duplicate? Table
schema, sample data, the code for the procedure maybe, desired results. See
http://www.aspfaq.com/5006
> Further investigation shows that the sql checks that a
> record doesn't have a "completed" status.
What datatype is this? How does sql "check"? What is the exact syntax you
are using? What data is actually stored in the column?
> Continuing the investigation, the developers run this SP
> through debug and find the value being returned
> = 'COMPLETED'. Run the *same* code in query analyzer and
> the value being returned is 'PLANNED'... How is this
> possible'
You're looking at a different row? The stored procedure is being executed
against the test database, and query analyzer is connected to production?
> And (cst.cst_consign_status_desc <> 'COMPLETED')
I'm going to guess that (shudder) "cst_consign_status_desc" is a CHAR
column. You should use VARCHAR so that trailing spaces are ignored, and
ensure that ANSI_PADDING is set the same in both environments to ensure that
you get consistent results.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/