How to get the top row value in insert of temporary table

Question

insert into @Temp([dateTime],Reading) 
values (
@startDate,
(select top(1) @dynamicCOLUMN from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))

I want the value of dynamic column instead it returns columns name in select subquery

Answer 1

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 startDate and dynamicCOLUMN are supplied via stored procedure parameters.

CREATE TABLE #Temp 
(
    [dateTime] datetime, 
    Reading varchar(100)
);

DECLARE 
    @sql nvarchar(MAX) = N'';

SELECT
    @sql += N'
SELECT
    @startDate,
    (
        SELECT TOP (1) 
            N' + QUOTENAME(@dynamicCOLUMN) + N' 
        FROM tableABC 
        WHERE DateColumn >= @startDate 
        AND DateColumn < @tempdt  
        ORDER by DateColumn desc
    );'

INSERT INTO  
    #Temp WITH (TABLOCK)
(
    [dateTime],
    Reading
) 
EXEC sys.sp_executesql
    @sql,
    N'@startDate datetime',
    @startDate;

• I use QUOTENAME to avoid potential SQL injection attempts
• I use TABLOCK to encourage a parallel plan on insert

Answer 2

If there are only a few possible columns, then you could do a separate check for each column name:

if (@dynamicCOLUMN = 'Column1')
begin
    insert into @Temp([dateTime],Reading) 
    values (
    @startDate,
    (select top 1 Column1 from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))
end

if (@dynamicCOLUMN = 'Column2')
begin
    insert into @Temp([dateTime],Reading) 
    values (
    @startDate,
    (select top 1 Column2 from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))
end

If there are a lot of possible columns, then dynamic queries as suggested by McNets may be more suitable.