0
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

1

2 Answers 2

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.