Save data to variable and use it in stored procedure I have a table of 2 columns (Date, Text). I am getting data from the Text column according to the Date using a query like this:

SELECT Text
FROM MyTable
WHERE Date&gt;= @userDate;


This works, except when @userDate is bigger than the most recent Date in MyTable. In this case it returns nothing but I need it to return the Text for the most recent Date.

I have to change my query to use the smaller one of @userDate and MAX(Date). Any ideas how to do it?
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&gt;= @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).