Substitute Column name in dynamic queries

Question

I have a procedure , in which i am receiving a parameter column name and creating the dynamic query by substituting column name.

when i am directly running this dynamic query its working fine.

declare @a datetime,
@b varchar(50) ='CREATED_DATE',--column name
@query varchar(500);
select  @a= CONVERT(datetime,LAST_RUN_PROC,121) from TEST_TABLE_MASTER


exec( 'select '+@b+' from TEST1 where '+@b+' = '+''''+@a+'''' )

But when i am storing query in a variable and then executing,its showing error. Below code showing error

declare @a datetime,
@b varchar(50) ='CREATED_DATE',--column name
@query varchar(500);
select  @a= CONVERT(datetime,LAST_RUN_PROC,121) from TEST_TABLE_MASTER

SET @query=  'select '+@b+' from TEST1 where '+@b+' = '+''''+@a+'''' --this line showing error Conversion failed when converting date and/or time from character string.

exec (@query)

I got stuck here.please help

Answer 1

1. Concatenating SQL string is not the best idea, because of multiple '''' needed. It is error-prone and hard to debug.

2. Use correct types (table name, column name) - SYSNAME, query -NVARCHAR(MAX).

3. You can use REPLACE placeholder to fill values or pass them as parameter to EXEC dbo.sp_executesql.

4. Always quote table/column names.

5. SELECT @a= CONVERT(datetime, LAST_RUN_PROC,121) FROM TEST_TABLE_MASTER; will set @a last read value from table, you should add TOP 1 and ORDER BY.

Code:

DECLARE @a DATETIME,
        @b SYSNAME            ='CREATED_DATE',
        @query NVARCHAR(MAX);

SELECT  @a= CONVERT(datetime, LAST_RUN_PROC,121) FROM TEST_TABLE_MASTER;

SET @query = 
   N'SELECT <col_name>
     FROM TEST1
     WHERE <col_name> = ''<col_value>'';';

SET @query = REPLACE(@query, '<col_name>', QUOTENAME(@b));     
SET @query = REPLACE(@query, '<col_value>', @a);  

--SELECT @query;

EXEC [dbo].[sp_executesql]
     @query;

SqlFiddleDemo

Recommended version with parameter passing and dbo.sp_executesql instead of EXEC:

DECLARE @a DATETIME,
        @b SYSNAME            ='CREATED_DATE',
        @query NVARCHAR(MAX);

SELECT @a= LAST_RUN_PROC FROM TEST_TABLE_MASTER;

SET @query = 
   N'SELECT <col_name>
     FROM TEST1
     WHERE <col_name> = @a;';

SET @query = REPLACE(@query, '<col_name>', QUOTENAME(@b));     

EXEC [dbo].[sp_executesql]
     @query
     ,N'@a DATETIME'
     ,@a;

SqlFiddleDemo2

Warning:

Using Dynamic-SQL is great resposibility. If you don't understand it, don't use Dynamic-SQL at all.

EDIT:

I've managed to run your example, but I strongly recommend to use one of the solution above:

SET @query=  'select '+@b+' from TEST1 where '+@b+' = '+''''+CONVERT(varchar(19),@a, 121)+'''' 

SqlFiddleDemo3