Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given

DECLARE @IN__CUST_NO    INT
DECLARE @OUT__USER_ID   INT
SET @IN__CUST_NO = 1000006660

CASE 1: IS NULL evaluates TRUE

Since no CUSTOMER_NUMBER with the value of 1000006660 exists in our dummy USERS-Table, stating a SELECT restricting to tuples of CUSTOMER_NUMBER-Columnvalues of 1000006660 leads to a zero-sized resultset. Hence IS NULL evaluates to TRUE in following statement issued to SSMS interactivly; resulting to PRINT 'null'

SELECT @OUT__USER_ID = u.ID
FROM USERS u 
WHERE u.CUSTOMER_NUMBER = @IN__CUST_NO

IF (@OUT__USER_ID IS NULL) 
BEGIN
    PRINT 'null'
END
ELSE
BEGIN
    PRINT 'set'
END

CASE 2: IS NULL evaluates FALSE

As above no CUSTOMER_NUMBER with the value of 1000006660 exists. But if one encapsulates the IS NULL part in a stored procedure like below and executes it, IS NULL will result to FALSE! (at least on my configuration; using SQL-Server 2005 and SSMS 2008). Endresult: PRINT 'set'

CREATE PROCEDURE SP_NULL_TEST
    @IN__CUST_NO        INT,
    @OUT__USER_ID       INT OUTPUT
AS
BEGIN
    SELECT @OUT__USER_ID = u.ID
    FROM USERS u 
    WHERE u.CUSTOMER_NUMBER = @IN__CUST_NO

    IF (@OUT__USER_ID IS NULL) 
    BEGIN
        PRINT 'null'
    END
    ELSE
    BEGIN
        PRINT 'set'
    END
END

EXEC SP_NULL_TEST @IN__CUST_NO, @OUT__USER_ID=@OUT__USER_ID

Please, can someone explain this strange behaviour? Many thanks in advance.

share|improve this question
    
I tested this using CREATE TABLE USERS (ID INT, CUSTOMER_NUMBER INT); then your code and I get 'null' in both cases. –  onedaywhen Feb 7 '12 at 10:26
3  
When no rows exist @OUT__USER_ID retains its previous value rather than being set explicitly to NULL. Sure in your test code you aren't assigning a value to the variable before doing the EXEC version? –  Martin Smith Feb 7 '12 at 10:48
1  
I've made a test script with your info but all the time I get: NULL, NULL... Are you sure you're not missing something :-)? –  Marian Feb 7 '12 at 11:05
    
Initializing @OUT__USER_ID with NULL solved the problem; @Martin –  exp'ix' Feb 7 '12 at 13:18
    
Please accept Erik's answer or provide your own and accept that :-) –  Jack Douglas Apr 7 '12 at 10:18
show 2 more comments

closed as too localized by Martin Smith, JNK, Mark Storey-Smith, gbn May 25 '12 at 15:41

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

Initialize @OUT__USER_ID with NULL, since it is probably being set to a non-null value earlier in your script somewhere.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.