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.

And is it possible to use this syntax even if the SELECT MAX(Date) FROM MyTable is in variable, because this query is built according to some other parameters
–
davidOct 26 '12 at 18:20

ya it can like IF @userDate>your variable
–
kumar_2002Oct 26 '12 at 18:21

Sorry I have asked bad question I mean what to do if I have somthing like this set @q ='SELECT MAX(Date) FROM MyTable' because I have to build the query as a string because the name of MyTable changes according to some paramters. So how could I get the Date when I have the string of query?
–
davidOct 26 '12 at 18:25

Hi edited according to your request, if you want to change table name you have to use dynamic T-sql .
–
kumar_2002Oct 26 '12 at 18:49

SELECT CASE WHEN T.Date IS NULL THEN M.TEXT ELSE T.TEXT END TEXT
FROM (SELECT TOP(1) *
FROM MyTable
ORDER BY Date DESC) M
LEFT JOIN MyTable T ON T.Date>= @userDate11

This is one of several ways to solve the problem. The TOP(1) and ORDER BY Date DESC gives you the fallback record. The original table aliased T is consigned as the optional partner in the LEFT JOIN. This allows it to return no records, while still retaining the singular record from the derived table aliased M. The CASE statement then tests to see if we actually managed to get anything from T using WHEN T.Date IS NULL - if T is not empty, all the TEXT values will come from T, otherwise we will have just the single TEXT value from M, being the one for the MAX(Date).