CODEWITHSUNDEEP
sqlsql-servert-sqldatestored-procedures
Sidhu177
Sidhu177

Reputation: 647

Passing NULL as Value in Parameter for T-SQL stored procedure

I have this stored procedure to which I pass a date parameter which could be NULL. The logic I am trying to get at is when the Date parameter is NULL then max of the date column should be chosen but when the parameter is not NULL then the parameter should be passed as the value. 

CREATE PROCEDURE [rdm].[Param_Load_Test2] 
    @Retro_Date DATE
AS
BEGIN
    SELECT 
        CASE  
           WHEN @Retro_Date IS NULL 
              THEN MAX(lnd.FILE_DATE) 
              ELSE @Retro_Date 
        END AS TEST_DATE
    INTO 
        #LoadTest1234
    FROM 
        TWV.TABLE_WITH_VALUES AS lnd
    GROUP BY 
        FILE_DATE;

    SELECT TEST_DATE 
    FROM #LoadTest1234  
END
GO

Now when I test the above piece of code with the EXEC statement for NULL case I get wrong values. The Not NULL case works fine.

EXEC rdm.Param_Load_Test2 NULL

>> 2018-03-31
>> 2018-05-31

I am expecting only one value which is 2018-05-31 but it returns 2 records instead. What am I doing wrong ?

Upvotes: 0

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You have a superfluous GROUP BY. I think you want:

        SELECT MAX(CASE WHEN @Retro_Date IS NULL THEN lnd.FILE_DATE ELSE @Retro_Date END) AS TEST_DATE
         INTO #LoadTest1234
         FROM TWV.TABLE_WITH_VALUES as lnd;

Or more simply:

        SELECT COALESCE(@Retro_Date, MAX(lnd.FILE_DATE)) AS TEST_DATE
        INTO #LoadTest1234
        FROM TWV.TABLE_WITH_VALUES as lnd;

I'm not sure why you would be using a temporary table for this. But I'm guessing this is part of more complicated logic.

Upvotes: 2

Related Questions