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
)