SQL Server 2008 R2 – CASE Statement Returning Different Results in COALESCE

sql-server-2008-r2

We're using SQL Server 2008 R2 with database compatibility set to SQL Server 2000, and I've got a table with a few rows from which I'm assigning values to multiple variables. I'm looking to switch from using separate SELECT statements to using a single one, and my code is working, but I'm not certain why it's working or that it's deterministic. My particular question relates to my use of CASE statements within a query, as illustrated in the code below. I've made a few comments in it, so I hope the question makes sense… Also, this is the first I'm posting here, so if I'm violating any understood protocol, please advise me of that.

    CREATE TABLE [dbo].[FinancialPeriod](
    [FinancialPeriodID] [int] IDENTITY(1,1) NOT NULL,
    [FinancialPeriod] [varchar](50) NULL,
 CONSTRAINT [PK_FinancialPeriod] PRIMARY KEY CLUSTERED 
(
    [FinancialPeriodID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
        -- THIS IS A STATIC TABLE CONTAINING THESE RECORDS, IN THIS ORDER 
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Month To Date')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Quarter To Date')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Year To Date')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Since Inception')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Trailing Month')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Trailing Quarter')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Trailing Year')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Trailing 3 Years')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Trailing 5 Years')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Trailing Year To Last Calendar Quarter')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Last Calendar Month')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Last Calendar Quarter')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Last Calendar Year')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Last Calendar 3 Years')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Last Calendar 5 Years')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Year To Last Calendar Quarter')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Inception To Last Calendar Quarter')
    INSERT FinancialPeriod (FinancialPeriod) VALUES('Custom Period')

DECLARE @Period1 VARCHAR(50)  = 'Last Qtr'
DECLARE @Period2 VARCHAR(50)  = NULL
DECLARE @Period3 VARCHAR(50)  = '  '
DECLARE @Period4 VARCHAR(50)  = 'Lst Yr'
DECLARE @Period5 VARCHAR(50)  = 'Last 3'
DECLARE @Period6 VARCHAR(50)
DECLARE @Period7 VARCHAR(50)  = ' '

DECLARE @fp1ID INTEGER = 12
DECLARE @fp2ID INTEGER = 16
DECLARE @fp3ID INTEGER = 10
DECLARE @fp4ID INTEGER = 13
DECLARE @fp5ID INTEGER = 14
DECLARE @fp6ID INTEGER = 17
DECLARE @fp7ID INTEGER = 99

/*  PREVIOUS CODE USE
SELECT @Period1 = FinancialPeriod FROM FinancialPeriod WHERE FinancialPeriodID = @fp1ID
SELECT @Period2 = FinancialPeriod FROM FinancialPeriod WHERE FinancialPeriodID = @fp2ID
SELECT @Period3 = FinancialPeriod FROM FinancialPeriod WHERE FinancialPeriodID = @fp3ID
SELECT @Period4 = FinancialPeriod FROM FinancialPeriod WHERE FinancialPeriodID = @fp4ID
SELECT @Period5 = FinancialPeriod FROM FinancialPeriod WHERE FinancialPeriodID = @fp5ID
SELECT @Period6 = FinancialPeriod FROM FinancialPeriod WHERE FinancialPeriodID = @fp6ID
SELECT @Period7 = FinancialPeriod FROM FinancialPeriod WHERE FinancialPeriodID = @fp7ID
*/

SELECT @Period1 =  COALESCE(NULLIF(@Period1, ''), CASE WHEN FinancialPeriodID = @fp1ID THEN FinancialPeriod ELSE '' END),
        @Period2 =  COALESCE(NULLIF(@Period2, ''), CASE WHEN FinancialPeriodID = @fp2ID THEN FinancialPeriod ELSE '' END), 
        @Period3 =  COALESCE(NULLIF(@Period3, ''), CASE WHEN FinancialPeriodID = @fp3ID THEN FinancialPeriod ELSE '' END), 
        @Period4 =  COALESCE(NULLIF(@Period4, ''), CASE WHEN FinancialPeriodID = @fp4ID THEN FinancialPeriod ELSE '' END), 
        @Period5 =  COALESCE(NULLIF(@Period5, ''), CASE WHEN FinancialPeriodID = @fp5ID THEN FinancialPeriod ELSE '' END), 
        @Period6 =  COALESCE(NULLIF(@Period6, ''), CASE WHEN FinancialPeriodID = @fp6ID THEN FinancialPeriod ELSE '' END), 
        @Period7 =  COALESCE(NULLIF(@Period7, ''), CASE WHEN FinancialPeriodID = @fp7ID THEN FinancialPeriod ELSE '' END)
    FROM FinancialPeriod

-- COMPARE THESE RESULTS
SELECT @Period1, @Period2, @Period3, @Period4, @Period5, @Period6, @Period7

SELECT @Period1 = CASE WHEN FinancialPeriodID = @fp1ID THEN FinancialPeriod ELSE '' END,
        @Period2 = CASE WHEN FinancialPeriodID = @fp2ID THEN FinancialPeriod ELSE '' END, 
        @Period3 = CASE WHEN FinancialPeriodID = @fp3ID THEN FinancialPeriod ELSE '' END, 
        @Period4 = CASE WHEN FinancialPeriodID = @fp4ID THEN FinancialPeriod ELSE '' END, 
        @Period5 = CASE WHEN FinancialPeriodID = @fp5ID THEN FinancialPeriod ELSE '' END, 
        @Period6 = CASE WHEN FinancialPeriodID = @fp6ID THEN FinancialPeriod ELSE '' END, 
        @Period7 = CASE WHEN FinancialPeriodID = @fp7ID THEN FinancialPeriod ELSE '' END
    FROM FinancialPeriod

-- TO THESE RESULTS... WHY THE DOES THE SECOND ASSIGNMENT RETURN DIFFERENT RESULTS?
SELECT @Period1, @Period2, @Period3, @Period4, @Period5, @Period6, @Period7

Best Answer

I don't think that your code is actually working (the first query). For instance, the @Period4 value for that is being returned as Lst Yr, when it should be Last Calendar Year. This happens because your use of COALESCE and the way you are giving the values to your variables. The SELECT @variable = value from table should return only one row, otherwise you are assigning whatever value on all the rows of the table, it's non deterministic. And the second query you are using compares @fpID with a value of the column FinancialPeriodID that can be whatever row, so it doesn't match and assigns a '' to your variable. What you should be doing instead is:

SELECT @Period1 = MAX(CASE WHEN FinancialPeriodID = @fp1ID THEN FinancialPeriod ELSE '' END),
       @Period2 = MAX(CASE WHEN FinancialPeriodID = @fp2ID THEN FinancialPeriod ELSE '' END), 
       @Period3 = MAX(CASE WHEN FinancialPeriodID = @fp3ID THEN FinancialPeriod ELSE '' END), 
       @Period4 = MAX(CASE WHEN FinancialPeriodID = @fp4ID THEN FinancialPeriod ELSE '' END), 
       @Period5 = MAX(CASE WHEN FinancialPeriodID = @fp5ID THEN FinancialPeriod ELSE '' END), 
       @Period6 = MAX(CASE WHEN FinancialPeriodID = @fp6ID THEN FinancialPeriod ELSE '' END), 
       @Period7 = MAX(CASE WHEN FinancialPeriodID = @fp7ID THEN FinancialPeriod ELSE '' END)
FROM #FinancialPeriod

Or 

SELECT @Period1 = COALESCE(@Period1,MAX(CASE WHEN FinancialPeriodID = @fp1ID THEN FinancialPeriod END),''),
       @Period2 = COALESCE(@Period2,MAX(CASE WHEN FinancialPeriodID = @fp2ID THEN FinancialPeriod END),''), 
       @Period3 = COALESCE(@Period3,MAX(CASE WHEN FinancialPeriodID = @fp3ID THEN FinancialPeriod END),''), 
       @Period4 = COALESCE(@Period4,MAX(CASE WHEN FinancialPeriodID = @fp4ID THEN FinancialPeriod END),''), 
       @Period5 = COALESCE(@Period5,MAX(CASE WHEN FinancialPeriodID = @fp5ID THEN FinancialPeriod END),''), 
       @Period6 = COALESCE(@Period6,MAX(CASE WHEN FinancialPeriodID = @fp6ID THEN FinancialPeriod END),''), 
       @Period7 = COALESCE(@Period7,MAX(CASE WHEN FinancialPeriodID = @fp7ID THEN FinancialPeriod END),'')
FROM #FinancialPeriod

This way you assign only the value when the match is done between FinancialPeriodID and @fpID.

Related Question