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.

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
share|improve this question
    
What results do you expect? Are the first results "right"? –  Aaron Bertrand Feb 24 at 17:14
    
The first results are what I want the query to do (i.e. non-blank for records where the period is in the table); I don't understand how or if SQL is ensuring that the value for the existing period is returned by the CASE instead of the blank value for the ELSE; does it evaluate the first WHEN condition across all rows prior to evaluating the next WHEN/ELSE condition? In trying to understand that behaviour, I removed the COALESCE just to simplify the scenario, but then the CASE behaved differently.... –  Michael Petersheim Feb 24 at 17:22
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks, Lamak; using MAX is a good idea. Concerning your #Period4 reference, I may not have explained correctly the desired behavior. The first query is actually working: I want it to use the existing value of #Period4 unless that value is a blank/empty/NULL string, in which case it returns the value of the corresponding period from the table. So yes, it should be returning 'Lst Yr' for #Period4. I'll try using MAX, as that is certainly deterministic; I'm still unclear on why my first query worked.... –  Michael Petersheim Feb 24 at 18:17
    
@MichaelPetersheim Your first query worked because is inherently different from the second. The first check is NULLIF(@Period4, ''), for @Period4 it returns 'Lst Yr', and so you are doing COALESCE('Lst Yr',something), so it will always be 'Lst Yr'. It doesn't work when you go to the second part of the COALESCE, the non deterministic part –  Lamak Feb 24 at 18:20
    
you're exactly right concerning Period4 specifically; Period2 is NULL and Period3 is blank, however, so those both fall through the COALESCE and fire the CASE. In the first query they end up with their respective values from the table, in the second query they end up blank; to me, it's clear that the CASE is behaving differently in these two queries. –  Michael Petersheim Feb 24 at 18:31
    
I like your edit to the second option you proposed, in which the empty string is added as a third COALESCE option instead of in the ELSE of the CASE. I will need to keep the NULLIF on the first parameter in COALESCE, however, because I want to look for a value in the table if the entry is non-null but blank. –  Michael Petersheim Feb 24 at 18:34
    
@MichaelPetersheim It's not the CASE expression the part that behaves differently, but the assigning of that result to the variable. The CASE expression behaves the same, giving a value for each row –  Lamak Feb 24 at 18:36
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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