SQL Case When Statement with multiple lines

Question

Is there a way to make this SQL smaller?

CASE
                    WHEN @contentType = 'PrimaryBannerItem' THEN
                        [dbo].[DeathStar_GetContentLink]         (@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, ''))

                    ELSE

                        [dbo].[DeathStar_GetContentImagePath](ISNULL(a.mimetype, ''), ISNULL(c.image, ''), ISNULL(l.filename, ''))
                END AS [Image],

                CASE
                    WHEN @contentType = 'PrimaryBannerItem' THEN
                         dbo.DeathStar_GetMetadataValue(c.content_id, @urlMetadataId)


                    ELSE

                        [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, ''))
                END AS Link,

To make it look more like

CASE
    WHEN @contentType = 'PrimaryBannerItem' THEN
        [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Image]
        dbo.DeathStar_GetMetadataValue(c.content_id, @urlMetadataId) as [Link]
    ELSE
        [dbo].[DeathStar_GetContentImagePath](ISNULL(a.mimetype, ''), ISNULL(c.image, ''), ISNULL(l.filename, '')) as [Image]
        [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Link]
END

Because as you can see, it makes the code a bit more readable, it cuts down on the number of lines used and it avoid redundancy.

Are you looking for a tool that will format your T-SQL for you? If so I've used SQL Prompt for a few years and have been fairly happy with it. http://www.red-gate.com/products/sql-development/sql-prompt/ — Tim Lentine, Jul 27 '12 at 14:57

score 1 · Answer 1 · answered Jul 29 '12 at 13:50

If the result sets returned in both cases are identical, perhaps you can play a little trick:

-- This query will return data only when @contentId = 'PrimaryBannerItem'
SELECT
  [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Image]
  ,dbo.DeathStar_GetMetadataValue(c.content_id, @urlMetadataId) as [Link]
  ,OtherFields
FROM
  YourTables
WHERE
  (@contentId = 'PrimaryBannerItem')
  -- Other WHERE clauses here

UNION ALL

-- This query will return data only when @contentId <> 'PrimaryBannerItem'
SELECT
  [dbo].[DeathStar_GetContentImagePath](ISNULL(a.mimetype, ''), ISNULL(c.image, ''), ISNULL(l.filename, '')) as [Image]
  ,[dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Link]
  ,OtherFields
FROM
  YourTables
WHERE
  (@contentId <> 'PrimaryBannerItem')
  -- Other WHERE clauses

This way, you should not need to use any IF, CASE or such.

Note: if you start having more complex conditions, such as values dependent on combinations of other parameters, this may not be the most efficient approach, both in terms of performance and, more importantly, maintenance.

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

Probably not.

The way that you are suggesting in your second code block will not work. CASE is a statement and can only be used to return the value for a single column. That is why you define the result of the CASE as a column, but cannot define columns in the case statement. Here's a SO that explains that.

The only possibility I can think of is to update your stored procedures/functions to allow null values, so that you can get rid of all your ISNULLs. Here's a link explaining this for a stored procedure.

edited May 23 '17 at 12:13

Community

1
1

answered Jul 27 '12 at 15:01

Daniel

12,982
3
36
60

Not trying to get rid of NULLS, just to return values into 2 columns at the same time and not have to make 2 different CASE cases. – Solid1Snake1 Jul 27 '12 at 17:25

Andriy M · Answer 3 · 2012-07-29T21:46:49.827

Not sure if this would be better, but there's probably no other option without having to repeat the entire query or use repetitive CASE ... WHEN ... ELSE ... expressions for every column, like in your example:

SELECT
  …
  COALESCE(choice1.Image, choice2.Image) AS Image,
  COALESCE(choice1.Link , choice2.Link ) AS Link,
  …
FROM
  …
OUTER APPLY (
  SELECT
    [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Image],
    dbo.DeathStar_GetMetadataValue(c.content_id, @urlMetadataId) as [Link]
  WHERE @contentType = 'PrimaryBannerItem'
) AS choice1
OUTER APPLY (
  SELECT
    [dbo].[DeathStar_GetContentImagePath](ISNULL(a.mimetype, ''), ISNULL(c.image, ''), ISNULL(l.filename, '')) as [Image],
    [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Link]
  WHERE @contentType <> 'PrimaryBannerItem'
    /* or, perhaps, "WHERE @contentType = 'something else'" */
) AS choice2
WHERE
  …

Between the two OUTER APPLYs, only one can return a row, the other will always return an empty row set and its columns will evaluate to NULL accordingly. That means you can return the "right" results using COALESCE: just specify corresponding columns in the necessary order, and the first non-NULL one will be returned.

score 0 · Answer 4 · answered Jul 27 '12 at 17:33

Is this solution possible?

-- First case
IF @contentType = 'PrimaryBannerItem' BEGIN
    SELECT [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Image],
    dbo.DeathStar_GetMetadataValue(c.content_id, @urlMetadataId) as [Link]

-- Second case
END ELSE BEGIN
    SELECT [dbo].[DeathStar_GetContentImagePath](ISNULL(a.mimetype, ''), ISNULL(c.image, ''), ISNULL(l.filename, '')) as [Image],
    [dbo].[DeathStar_GetContentLink](@contentId, c.content_id, @pageCollectionId, ISNULL(l.filename, '')) as [Link]
END

If these are the only fields you're selecting, it certainly would look cleaner. However I'm pretty sure I'm not seeing your entire code, so this may not give you exactly what you want.

Even if they were the only fields returned, this would not work, because some of the arguments are column references (or expressions containing column references). — Andriy M, Jul 27 '12 at 17:35
Understood - I think, on the balance, you either want to do two full selects in an IF statement (if the number of conditional columns is high) or CASE columns for everything (if the number of non-conditional columns is high). — Ted Spence, Jul 27 '12 at 19:28

score 0 · Accepted Answer · answered Jul 27 '12 at 21:34

0

No. The CASE expression returns a scalar, a single value. It cannot return two values; it can't return values for two columns.

answered Jul 27 '12 at 21:34

spencer7593

106,611
15
112
140

SQL Case When Statement with multiple lines

5 Answers5