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 AS TEXT
FROM (
 SELECT TOP(1) *
 FROM MyTable
 ORDER BY Date DESC
 ) AS 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).