January 17, 2013 at 1:15 pm
I have a stored procedure where the following appears:
SELECT @MgrApproved = CASE WHEN @MgrApprovalDate IS NULL THEN 0 ELSE 1 END
As I understand the CASE statement, @MgrApproved should never be anything other than 0 or 1. There's no way it can possibly be NULL. And yet, NULL is exactly what I'm getting.
I just moved a bunch of things from QA to production last night, and things that were working in QA are breaking in production because of this. Both SQL servers are 9.0.4035. When I check server properties, the only difference I see, other than the server name is that the production server has a bit more than twice as much memory, which can't produce NULL values wrongly, last time I checked.
My codebase is riddled with this kind of statement. I tried changing it to this:
IF @MgrApprovalDate IS NULL
SET @MgrApproved = 0
ELSE
SET @MgrApproved = 1
Same results. Can anyone explain to me what's happening?
January 17, 2013 at 1:22 pm
Can you print both variables before and after the 'select'. How does the date variable get set? What are the data types for the variables?
January 17, 2013 at 3:47 pm
Agreed. If you can provide more of the code we will have a better chance at an answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2013 at 4:04 pm
3 votes for the actual query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2013 at 4:11 pm
Forgive me for wasting your time. Someone had started adding code into my proc with an IF statement, and hadn't closed it or commented it out. What a waste. Thanks for your help, though.
January 17, 2013 at 4:40 pm
Lisa Liel (1/17/2013)
Forgive me for wasting your time. Someone had started adding code into my proc with an IF statement, and hadn't closed it or commented it out. What a waste. Thanks for your help, though.
Fair enough. Glad you found it
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply