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.
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@OUT__USER_IDretains its previous value rather than being set explicitly toNULL. Sure in your test code you aren't assigning a value to the variable before doing theEXECversion? – Martin Smith Feb 7 '12 at 10:48@OUT__USER_IDwithNULLsolved the problem; @Martin – exp'ix' Feb 7 '12 at 13:18