修改SQL存储过程：将Reported As列的-999.99替换为<3显示

报表输出的Reported As列当前显示值为-999.99，需要将其替换为<3.0显示。以下是当前正在使用的spRp20DataListData存储过程代码，同时提供另一个相关存储过程作为参考，请求修改建议。

USE [HarvestSQL]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[spRp20DataListData]
    @date1 DATETIME,
    @date2 DATETIME,
    @location VARCHAR(255),
    @patients NTEXT
AS

        EXEC ('
        SELECT 
            * 
        FROM 
            vwReportPortalMajorTest 
        WHERE 
            (DrawDate >= ''3/1/2006 00:00:00'') AND 
            (DrawDate < ''5/7/2006 00:00:00'') AND 
            (DrawLocation = ''Ameri-Tech Kidney Center Arlington'')
        ')

    DECLARE @date1String VARCHAR(40)
    DECLARE @date2String VARCHAR(40)
    SET  @date1String = CONVERT(VARCHAR(40),@date1,109)
    SET  @date2String = CONVERT(VARCHAR(40),@date2,109)
    IF @location = 'Charleston Renal Care' 
    BEGIN
        SET @location = 'Liberty Dialysis Petersburg'
    END
        EXEC ('
        SELECT 
            * 
        FROM 
            vwRp20MajorTest 
        WHERE 
            (DrawDate >= ''' + @date1String + ''') AND 
            (DrawDate < ''' + @date2String + ''') AND 
            (DrawLocation =  '''  + @location + ''')
        ')

USE [ReportPortal]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   PROC [dbo].[spRp20DataListData]
    @date1 DATETIME,
    @date2 DATETIME,
    @location VARCHAR(1000),
    @patients NTEXT,
    @dynamicSqlSelectClause NTEXT,
    @dynamicSqlGroupByClause NTEXT,
    @suppressEmptyRows VARCHAR(10) = NULL,
    @resultsToQuery INT = 0
AS
    DECLARE @date1String VARCHAR(40)
    DECLARE @date2String VARCHAR(40)
    
    set @date2 = DateAdd(day, 1, @date2)

    SET @date1String = CONVERT(VARCHAR(40),@date1,109)
    SET @date2String = CONVERT(VARCHAR(40),@date2,109)
print @location

-- print SUBSTRING(@location,0, 7) 
-- print SUBSTRING(@location,7, (len(@location)-6))


IF SUBSTRING(@location,0, 12)  = 'Locations: '
    BEGIN
    print 'Running for Multiple Locations'
    set @location = SUBSTRING(@location,12, (len(@location)-11))
    print '@location updated to'
    print @location
    IF @resultsToQuery = 0 --All results
        BEGIN
            EXEC ('
                SELECT 
                    ' + @dynamicSqlSelectClause +  --PatientName,DrawDate,MAX((CASE WHEN NAME=''Albumin'' THEN NumberResult ELSE 0 END)) AS AlbuminResult
                ', PatientKey FROM 
                    vwRp20CompletedTest 
                WHERE 
                    DrawDate >= ''' + @date1String + ''' AND 
                    DrawDate < ''' + @date2String + ''' AND 
                    DrawAbbrev IN  (' + @location + ') AND
                    PatientKey IN ( ' + @patients + ') AND
                    ((NumberResult <> 0) OR (Name like ''%Hepatitis%'')) '
                +  'GROUP BY ' + @dynamicSqlGroupByClause + ', PatientKey'
                )
        END
    ELSE
        BEGIN
            EXEC ('
                SELECT 
                    ' + @dynamicSqlSelectClause + --PatientName,DrawDate,MAX((CASE WHEN NAME=''Albumin'' THEN NumberResult ELSE 0 END)) AS AlbuminResult
                ', PatientKey FROM 
                    vwRp20MajorTest 
                WHERE 
                    DrawDate >= ''' + @date1String + ''' AND 
                    DrawDate < ''' + @date2String + ''' AND 
                    DrawAbbrev IN  (' + @location + ') AND
                    PatientKey IN ( ' + @patients + ') AND
                    ((NumberResult <> 0) OR (Name like ''%Hepatitis%'')) '
                +  'GROUP BY ' + @dynamicSqlGroupByClause + ', PatientKey'                      
                )
        END
    END
ELSE
    BEGIN
    print 'Running for Single Location'
    IF @resultsToQuery = 0 --All results
        BEGIN
            EXEC ('
                SELECT 
                    ' + @dynamicSqlSelectClause + --PatientName,DrawDate,MAX((CASE WHEN NAME=''Albumin'' THEN NumberResult ELSE 0 END)) AS AlbuminResult
                ', PatientKey FROM 
                    vwRp20CompletedTest 
                WHERE 
                    DrawDate >= ''' + @date1String + ''' AND 
                    DrawDate < ''' + @date2String + ''' AND 
                    DrawLocation =  ''' + @location + ''' AND
                    PatientKey IN ( ' + @patients + ') ' --Ended statement here 07032019 added single quote here.
                --  AND    --removed all this because the number result is breaking many reports. 
                --  ((NumberResult > 2.9) 
                --  OR (Name like ''%Hepatitis%'')) '-- unsure if removing this Hepatitis will break reports.
                +  'GROUP BY ' + @dynamicSqlGroupByClause  + ', PatientKey'
                + ' ORDER BY ' +  @dynamicSqlGroupByClause 
                )
        END
    ELSE
        BEGIN
            EXEC ('
                SELECT 
                    ' + @dynamicSqlSelectClause + --PatientName,DrawDate,MAX((CASE WHEN NAME=''Albumin'' THEN NumberResult ELSE 0 END)) AS AlbuminResult
                ' ,PatientKey FROM 
                    vwRp20MajorTest 
                WHERE 
                    DrawDate >= ''' + @date1String + ''' AND 
                    DrawDate < ''' + @date2String + ''' AND 
                    DrawLocation =  ''' + @location + ''' AND
                    PatientKey IN ( ' + @patients + ') AND
                    ((NumberResult <> 0) OR (Name like ''%Hepatitis%'')) '
                +  'GROUP BY ' + @dynamicSqlGroupByClause + ', PatientKey'                      
                )
        END 
END

针对正在使用的存储过程，核心是修改动态SQL中的SELECT语句，对Reported As列做条件替换：

方案1：显式指定列并处理目标值 #

将第二个EXEC中的SELECT *替换为显式列名+CASE判断，确保精准控制输出：

EXEC ('
SELECT 
    -- 替换为vwRp20MajorTest视图中的实际列名，示例如下
    DrawDate, DrawLocation, PatientID,
    CASE WHEN [Reported As] = -999.99 THEN ''<3.0'' ELSE CAST([Reported As] AS VARCHAR(20)) END AS [Reported As]
FROM 
    vwRp20MajorTest 
WHERE 
    (DrawDate &gt;= ''' + @date1String + ''') AND 
    (DrawDate &lt; ''' + @date2String + ''') AND 
    (DrawLocation =  '''  + @location + ''')
')

方案2：保留所有列并新增替换后的列 #

如果不想逐个列出列，可保留原所有列的同时新增处理后的列，后续报表使用新列即可：

EXEC ('
SELECT 
    *,
    CASE WHEN [Reported As] = -999.99 THEN ''<3.0'' ELSE CAST([Reported As] AS VARCHAR(20)) END AS [Reported As_Updated]
FROM 
    vwRp20MajorTest 
WHERE 
    (DrawDate &gt;= ''' + @date1String + ''') AND 
    (DrawDate &lt; ''' + @date2String + ''') AND 
    (DrawLocation =  '''  + @location + ''')
')

注意事项：

• 若Reported As不是数值类型，需调整条件判断的写法
• 动态SQL中字符串单引号需转义，因此<3.0要写成''<3.0''

内容的提问来源于stack exchange，提问作者ktestcody