Save data to variable and use it in stored procedure

Question

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>= @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?

Answer 1

DECLARE @tablename varchar(100)='site'
DECLARE @table1 table(date1 date)
INSERT INTO @table1
EXEC ('SELECT MAX(insdt) AS date  FROM '+@tablename+'')
DECLARE @yourvariable date
SELECT @yourvariable=date1 FROM @table1

DECLARE @userDate date

IF @userDate>@yourvariable
BEGIN
   SELECT  top 1 text FROM MyTable ORDER BY [date] DESC
END
ELSE 
BEGIN
   SELECT Text FROM MyTable WHERE Date>= @userDate
END

Answer 2

   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).