SQL Server Dynamic sql with CASE expression

dfty9e19 于 2023-02-18 发布在其他

关注(0)|答案(2)|浏览(101)

SET @SQL =
        'SELECT
        CaseStatus =
            CASE Level1Status
                WHEN 1100 THEN ''Case Submitted to QC''
                WHEN 1200 THEN ''Pending QC''
                WHEN 1400 THEN ''Passed QC''
            
            END,

I'm currently having problems with the dynamic sql/case expression above , as I can't seem to put strings inside dynamic sql, does anyone happen to have a solution to fixing this

EDIT

AS BEGIN DECLARE @SQL nvarchar(4000)

SET @SQL =
    'SELECT
    CaseStatus =
        CASE Level1Status
            WHEN 1100 THEN ''Case Submitted to QC''
            WHEN 1200 THEN ''Pending QC''
            WHEN 1400 THEN ''Passed QC''
        
        END,
        
        CaseStartDateTime,
        CaseEndDateTime,
        StatusName,
        Cell_NameDescription,
        QCAnalystName,
        AnalystName,
        Upload_Datetime,
        Requesting_Entity,
        Legal_Entity_TypeDescription,
        HighPriorityDescription,
        DD_Level_RequiredDescription,
        CountryDscr,
        Maintable.KYCCaseId AS KYCCaseId
    FROM
        UACTc75760ab10784b51b585f082d4b25223 AS MI,
        UACT175e55161660402692a53a4cdeb89bd6 AS MainTable,
        UACT5996d6e5151245cab24e4e76e3e53540 AS Statuses,
        UACTde5f05df6c5f4872a1e57b3cf8368301 AS AddressDetails
    WHERE
        (
        MI.CaseStartDateTime BETWEEN ' + quotename(convert(varchar(10), @CaseStartDateTime, 120), '''') + ' AND '  + quotename(convert(varchar(10), @CaseEndDateTime, 120), '''') +
        ' OR
        MI.CaseEndDateTime BETWEEN ' + quotename(convert(varchar(10), @CaseStartDateTime, 120), '''') +  ' AND ' + quotename(convert(varchar(10), @CaseEndDateTime, 120), '''') +
        ' )
    AND
        MI.KYCCase_Id = MainTable.KYCCaseId'
    IF @StatusName IS NOT NULL AND ltrim(rtrim(@StatusName)) != N''
        SET @SQL = @SQL + ' 
        AND
            Statuses.SourceStatus = MainTable.Level1Status
        AND
            Statuses.StatusName = ' + quotename(@StatusName, '''')
    IF @CountryDscr IS NOT NULL AND ltrim(rtrim(@CountryDscr)) != N''
    SET @SQL = @SQL + '
        AND
            AddressDetails.CountryDscr = ' + quotename(@CountryDscr, '''')      
    IF @CellDscr IS NOT NULL AND ltrim(rtrim(@CellDscr)) != N''
        SET @SQL = @SQL + ' 
        AND
            MainTable.Cell_NameDescription = ' + quotename(@CellDscr, '''') 
    IF @QCAnalystName IS NOT NULL AND ltrim(rtrim(@QCAnalystName)) != N''
        SET @SQL = @SQL + '
        AND
            MainTable.QCAnalystName = ' + quotename(@QCAnalystName, '''')   
    IF @AnalystName IS NOT NULL AND ltrim(rtrim(@AnalystName)) != N''
        SET @SQL = @SQL + '
        AND
            MainTable.AnalystName = ' + quotename(@AnalystName, '''')
    IF @RequestingEntity IS NOT NULL AND ltrim(rtrim(@RequestingEntity)) != N''
        SET @SQL = @SQL + '
        AND
            MainTable.Requesting_Entity = ' + quotename(@RequestingEntity, '''')
    IF @EntityType IS NOT NULL AND ltrim(rtrim(@EntityType)) != N''
        SET @SQL = @SQL + '
        AND
            MainTable.Legal_Entity_TypeDescription = ' + quotename(@EntityType, '''')
    IF @HighPriority IS NOT NULL AND ltrim(rtrim(@HighPriority)) != N''
        SET @SQL = @SQL + '
        AND
            MainTable.HighPriorityDescription = ' + quotename(@HighPriority, '''')
    IF @DDLevelRequired IS NOT NULL AND ltrim(rtrim(@DDLevelRequired)) != N''
        SET @SQL = @SQL + '
        AND
            MainTable.DD_Level_RequiredDescription = ' + quotename(@DDLevelRequired, '''')  

EXEC sp_executesql @SQL

This is the entire code, The error I'm getting is

Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'Case'.

sql-server

来源：https://stackoverflow.com/questions/31023837/dynamic-sql-with-case-expression

2条答案

按热度按时间

qhhrdooz1#

This is working fine .

Create Table #Level1Status(Id int identity ,Level1Status int)

Insert INto #Level1Status(Level1Status) values(1100),(1200),(1400)

            Declare @SQL Nvarchar(Max)
            SET @SQL =
                    'SELECT 
                    CaseStatus =
                        CASE Level1Status
                            WHEN 1100 THEN ''Case Submitted to QC''
                            WHEN 1200 THEN ''Pending QC''
                            WHEN 1400 THEN ''Passed QC''

                        END from #Level1Status';
            Exec SP_ExecuteSQL @SQL

赞(0）回复(0）举报 2023-02-18

jexiocij2#

Since you are concatenate your @SQL string with local variable "@xxx", in case any of thease may null, It want give any output so use ISNULL() or COALESCE() at concatenation with all local vaiable you used.

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server Dynamic sql with CASE expression

2条答案

相关问题

热门标签

最新问答