Error on executing sql statement using CASE-CodePudding

I am creating a SQL statement as string in a variable and it supposedly return a new column as Deadline_week_test but it wrongly refer to the column name from statement

DECLARE @ColName varchar(max) = 'Deadline'

DECLARE @test_column varchar(max) = 'CASE 
    WHEN DATEPART("dw",' @ColName ') = 1 THEN "Sunday" 
    WHEN DATEPART("dw",' @ColName ') = 2 THEN "Monday" 
    WHEN DATEPART("dw",' @ColName ') = 3 THEN "Tuesday" 
    WHEN DATEPART("dw",' @ColName ') = 4 THEN "Wednesday" 
    WHEN DATEPART("dw",' @ColName ') = 5 THEN "Thursday" 
    WHEN DATEPART("dw",' @ColName ') = 6 THEN "Friday" 
    WHEN DATEPART("dw",' @ColName ') = 7 THEN "Saturday" 
    ELSE "NULL" END AS Deadline_week_test '

    
DECLARE @sql as nvarchar(max) = ('select ' @test_column 'from dbo.SalesHeader');

exec sp_executesql @sql

CodePudding user response：

Datepart first parameter doesn't need quotes. Also for the week day replace double quote with two single quotes.

DECLARE @ColName varchar(max) = 'Deadline'

DECLARE @test_column varchar(max) = 'CASE 
    WHEN DATEPART(dw,' @ColName ') = 1 THEN ''Sunday''
    WHEN DATEPART(dw,' @ColName ') = 2 THEN ''Monday''
    WHEN DATEPART(dw,' @ColName ') = 3 THEN ''Tuesday'' 
    WHEN DATEPART(dw,' @ColName ') = 4 THEN ''Wednesday'' 
    WHEN DATEPART(dw,' @ColName ') = 5 THEN ''Thursday''
    WHEN DATEPART(dw,' @ColName ') = 6 THEN ''Friday''
    WHEN DATEPART(dw,' @ColName ') = 7 THEN ''Saturday'' 
    ELSE NULL END AS Deadline_week_test '

    
DECLARE @sql as nvarchar(max) = ('select ' @test_column 'from dbo.SalesHeader');

exec sp_executesql @sql

CodePudding user response：

Replace our double quotes to 2 single quotes.

DECLARE @ColName varchar(max) = 'Deadline'

DECLARE @test_column varchar(max) = 'CASE 
    WHEN DATEPART(''dw'',' @ColName ') = 1 THEN ''Sunday'' 
    WHEN DATEPART(''dw'',' @ColName ') = 2 THEN ''Monday'' 
    WHEN DATEPART(''dw'',' @ColName ') = 3 THEN ''Tuesday'' 
    WHEN DATEPART(''dw'',' @ColName ') = 4 THEN ''Wednesday'' 
    WHEN DATEPART(''dw'',' @ColName ') = 5 THEN ''Thursday'' 
    WHEN DATEPART(''dw'',' @ColName ') = 6 THEN ''Friday'' 
    WHEN DATEPART(''dw'',' @ColName ') = 7 THEN ''Saturday'' 
    ELSE ''NULL'' END AS Deadline_week_test '

    
DECLARE @sql as nvarchar(max) = ('select ' @test_column 'from dbo.SalesHeader');

exec sp_executesql @sql