Case Question

Transact-SQL
1

Hi

I want to check if record is avaiable in the language request if not give english version.

SELECT explanation FROM dbo.T_Payment_Options_Explanations
WHERE (T_Payment_Options_Explanations.lang =
(CASE WHEN @lang IS NULL THEN 'en' ELSE @lang END))

I tried this without success.
any help.

Thanks a lot

2

can you try this?

SELECT explanation,CASE WHEN @lang IS NULL THEN 'en'
ELSE @lang END as Language
FROM dbo.T_Payment_Options_Explanations
WHERE T_Payment_Options_Explanations.lang =@lang

3

Shouldn't

WHERE T_Payment_Options_Explanations.lang = COALESCE(@lang, 'en')

(in the original query) be sufficient?

1 Like
4

When you said "without success" what did you mean? Did you get an error message, or are you not getting any results at all, or the wrong results?

The statement looks syntactically correct to me - although I like Kristen's version better because it is compact.

5

This doesnt work

It doesnt return the english record.

Maybe im taking the wrong aproach here.

i used to do this
Declare @counter as int
SELECT count(*) FROM dbo.T_Payment_Options_Explanations
WHERE lang=@lang
If @counter>0 then
SELECT explanation FROM dbo.T_Payment_Options_Explanations
WHERE lang=@lang
else
SELECT explanation FROM dbo.T_Payment_Options_Explanations
WHERE lang='en'

but there must be a beter solution

Thanks

6

What happens when you use that? Do you get an error message, or are you not getting any results at all, or the wrong result? If you are getting an error message, what is the error message?

7

sorry edit me previous post

8

Are you using Microsoft SQL Server? The reason I am asking is because the syntax is incorrect for Microsoft's variant of SQL language. If you are using another RDBS, you might be better off asking the question in a forum for that vendor. This forum is for Microsoft SQL Server, and there would be few if any who are well-versed with other products.

9 
Declare @counter as int
SELECT @counter = count(*) FROM dbo.T_Payment_Options_Explanations
WHERE lang=@lang
If @counter>0 then
SELECT explanation FROM dbo.T_Payment_Options_Explanations
WHERE lang=@lang
else
SELECT explanation FROM dbo.T_Payment_Options_Explanations
WHERE lang='en'

is basically saying "How many language records match @lang?

If ZERO then retrieve the record for 'en', otherwise retrieve the record for @lang. (There are some issues with this approach which I will describe later).

Your new approach (and the variations / improvements we have suggested is:

SELECT explanation FROM dbo.T_Payment_Options_Explanations
WHERE (T_Payment_Options_Explanations.lang =
(CASE WHEN @lang IS NULL THEN 'en' ELSE @lang END))

which says If @lang IS NULL - i.e. exactly matches NULL and no other value - then fetch 'en', otherwise fetch @lang.

So your original way will fetch 'en' if there is ANY value for @lang (NULL, Blank, "FooBar"!) which cannot be found in the table.

I think the best way (but others my have even better ways) to "get @lang if it exists, otherwise get 'en' " is:

SELECT TOP 1
FROM
(
    SELECT 1 AS T_Order, explanation
    FROM dbo.T_Payment_Options_Explanations
    WHERE T_Payment_Options_Explanations.lang = @lang
    UNION ALL
    SELECT 2 AS T_Order, explanation
    FROM dbo.T_Payment_Options_Explanations
    WHERE T_Payment_Options_Explanations.lang = 'en'
) AS X
ORDER BY T_Order

If you are actually selecting [explanation] into a @Variable then I would do this differently using two separate queries, but that will give two resultsets - which will muck things up if both resultsets are sent directly into an Application

The issue I have with your COUNT(*) approach is that SQL has to count all the rows before it can decide what to do. There isn't going to be more than 1 row on a match (we hope!) so I suppose that isn't much work, but for ZERO rows I suspect that SQL will test EVERY row in the table looking for a match. It might be that SQL will use an Index and decide there isn't a match ...

Generally for this type of situation using EXISTS is much more efficient than using COUNT(*). For EXISTS (or NOT EXISTS) SQL knows it only has to find one matching row ... so it can build the query efficiently based on looking for that outcome, rather than expecting to be counting "several" rows for the result.

My suggested code might work better as:

SELECT explanation
FROM dbo.T_Payment_Options_Explanations
WHERE T_Payment_Options_Explanations.lang = @lang
UNION ALL
SELECT explanation
FROM dbo.T_Payment_Options_Explanations
WHERE T_Payment_Options_Explanations.lang = 'en'
AND NOT EXISTS
(
    SELECT *
    FROM dbo.T_Payment_Options_Explanations
    WHERE T_Payment_Options_Explanations.lang = @lang
)
1 Like
10

This might work efficiently too (but I always worry that OR in the WHERE clause will be slow):

SELECT TOP 1 explanation
FROM dbo.T_Payment_Options_Explanations
WHERE    T_Payment_Options_Explanations.lang = @lang
      OR T_Payment_Options_Explanations.lang = 'en'
ORDER BY CASE WHEN T_Payment_Options_Explanations.lang = 'en' THEN 2 ELSE 1 END
1 Like
11

Hi Kristen thanks a lot for the explanation.
the @lang var has always a value. get it from user browser.
okay will not use count anymore :wink:

The last one you gave worked well.
there not that many records, since there another filter in the where clause explanation_id so it will only choose between a few.

Thanks a lot
Mike

12

it is ms sql 2008, i just typed out of my head