This is an example of using safe dynamic SQL to accomplish the task. I've tweaked the code to use a
#temp table rather than a
@table variable, because the latter can contribute to all sorts of weird performance issues, depending on usage.
I've made some guesses, and you may need to adjust to meet local conditions. I'm assuming that
dynamicCOLUMN are supplied via stored procedure parameters.
CREATE TABLE #Temp
@sql nvarchar(MAX) = N'';
@sql += N'
SELECT TOP (1)
N' + QUOTENAME(@dynamicCOLUMN) + N'
WHERE DateColumn >= @startDate
AND DateColumn < @tempdt
ORDER by DateColumn desc
#Temp WITH (TABLOCK)
- I use QUOTENAME to avoid potential SQL injection attempts
- I use TABLOCK to encourage a parallel plan on insert