时间戳

首页 > 解决方案 > 什么是 VARCHAR(MAX) = NULL 和 fnStringSplit？

sql-server-2008 - 什么是 VARCHAR(MAX) = NULL 和 fnStringSplit？

问题描述

这是从存储过程中获取的。我不确定 where 语句后半部分的代码在做什么。@DivisionList没有价值时如何使用？是什么fnStringSplit？任何帮助都会很棒。谢谢

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
              
    ALTER  PROCEDURE [dbo].[spAppGetSAMDataForBARCValidationAudit]
@DivisionList VARCHAR(MAX) = NULL,
@RegionList VARCHAR(MAX) = NULL,
@PlanIDList VARCHAR(MAX) = NULL,
@LastUpdateByID CHAR(7),
@SyncType CHAR(15) = NULL
    AS
    BEGIN

BEGIN TRY
    IF (@SyncType = 'BARCtoMAAUI')
    BEGIN                       
        IF (SELECT  OBJECT_ID ('tempdb..#tmpTPFMRA2')) IS NOT NULL
        BEGIN
            DROP TABLE #tmpTPFMRA2;
        END;
        SELECT DISTINCT
               PlanYearID,
               CPS AS BidYearCPBPS,
               LastUpdateDateTime AS [date]
        INTO #tmpTPFMRA2
        FROM [dbo].[Trend_SavedPopulationBarcBidYearMembership] WITH (NOLOCK);

        IF (SELECT  OBJECT_ID ('tempdb..#tmpTPFMarketAdj2')) IS NOT NULL
        BEGIN
            DROP TABLE #tmpTPFMarketAdj2;
        END;
        SELECT DISTINCT
               CPS AS BidYearCPBPS,
               LastUpdateDateTime AS [date]
        INTO #tmpTPFMarketAdj2
        FROM [dbo].[Trend_SavedPopulationMarketAdjustment] WITH (NOLOCK);             

        --GET bidyear value 
        DECLARE @BidYear2 INT;
        SELECT @BidYear2 = dbo.fnGetBidYear();

        SELECT DISTINCT
               pl.PlanYear,
               pl.CPS,
               pl.LastCPS,
               ISNULL(pl.PlanInfoID, 0) PlanInfoID,
               pl.Division,
               pl.Region,
               pl.Product,
               pl.SNPType,
               pl.PlanType,
               ISNULL(pl.IsHidden, 0) IsHidden,
               ISNULL(cw.BidYear, 0) CWBidYear,
               cw.SSStateCountyCD,
               cw.BaseYearCPS,
               cw.CurrentYearCPS,
               cw.BidYearCPS,
               ISNULL(cw.ServiceAreaOptionID, 0) ServiceAreaOptionID,
               cw.ServiceAreaOptionName,
               cw.ServiceAreaOptionDescription,
               ISNULL(cw.BidYearRenewalTypeID, 0) BidYearRenewalTypeID,
               ISNULL(cw.BidYearPlanInfoID, 0) BidYearPlanInfoID,
               ISNULL(cw.CurrentYearPlanInfoID, 0) CurrentYearPlanInfoID,
               ISNULL(cw.IsActive, 0) IsActive,
               ISNULL(tpfMRA.[date], 0) AS MRALastUpdatedTime,
               ISNULL(tpfMarketAdj.[date], 0) AS MarketAdjLastUpdatedTime,                    
               @BidYear2 AS BidYear
       
        FROM vwPlanInfo pl WITH (NOLOCK)
            LEFT JOIN
            (
                SELECT DISTINCT
                       BidYear,
                       SSStateCountyCD,
                       BaseYearCPS,
                       CurrentYearCPS,
                       BidYearCPS,
                       ServiceAreaOptionID,
                       ServiceAreaOptionName,
                       ServiceAreaOptionDescription,
                       BidYearRenewalTypeID,
                       BidYearPlanInfoID,
                       CurrentYearPlanInfoID,
                       IsActive
                FROM vwSAMCrosswalks WITH (NOLOCK)
            ) cw
                ON pl.PlanInfoID = cw.BidYearPlanInfoID
                   AND pl.PlanYear = cw.BidYear                 
            LEFT JOIN #tmpTPFMRA2 tpfMRA WITH (NOLOCK)
                ON pl.PlanYear = tpfMRA.PlanYearID
                   AND pl.CPS = tpfMRA.BidYearCPBPS
            LEFT JOIN #tmpTPFMarketAdj2 tpfMarketAdj WITH (NOLOCK)
                ON pl.CPS = tpfMarketAdj.BidYearCPBPS                
        WHERE pl.PlanYear >
        (
            SELECT MIN(PlanYear) - 3
            FROM LkpPlanYear WITH (NOLOCK)
            WHERE IsProjectedYear = 1
        )
              AND
              (
                  (@DivisionList IS NULL)
                  OR pl.Division IN
                     (
                         SELECT Value FROM dbo.fnStringSplit(@DivisionList, ',')
                     )
              )
              AND
              (
                  (@RegionList IS NULL)
                  OR pl.Region IN
                     (
                         SELECT Value FROM dbo.fnStringSplit(@RegionList, ',')
                     )
              )
              AND
              (
                  (@PlanIDList IS NULL)
                  OR pl.PlanInfoID IN
                     (
                         SELECT DISTINCT Value FROM dbo.fnStringSplit(@PlanIDList, ',')
                     )
              );             
    END;

   ELSE
    BEGIN

        SELECT DISTINCT
               PlanYearID,
               CPS AS BidYearCPBPS,
               LastUpdateDateTime AS [date]
        INTO #tmpTPFMRA
        FROM [dbo].[Trend_SavedPopulationBarcBidYearMembership] WITH (NOLOCK);

        SELECT DISTINCT
               CPS AS BidYearCPBPS,
               LastUpdateDateTime AS [date]
        INTO #tmpTPFMarketAdj
        FROM [dbo].[Trend_SavedPopulationMarketAdjustment] WITH (NOLOCK);

        SELECT DISTINCT
               cs.PlanInfoID,
               cs.ServiceAreaOptionID,
               cs.BasePlanInfoID,
               cs.Status,
               cs.RemovedMM,
               cs.MovingMM,
               cs.TotalMM,
               cs.SignificancePct,
               cs.IsWks1BasePlan
        INTO #tmpCalcSign
        FROM dbo.CalcSignificance cs WITH (NOLOCK);

        --GET bidyear value 
        DECLARE @BidYear INT;
        SELECT @BidYear = dbo.fnGetBidYear();

        SELECT DISTINCT
               pl.PlanYear,
               pl.CPS,
               pl.LastCPS,
               pl.PlanInfoID,
               pl.Division,
               pl.Region,
               pl.Product,
               pl.SNPType,
               pl.PlanType,
               ISNULL(pl.IsHidden, 0) IsHidden,
               ISNULL(cw.BidYear, 0) CWBidYear,
               cw.SSStateCountyCD,
               cw.BaseYearCPS,
               cw.CurrentYearCPS,
               cw.BidYearCPS,
               ISNULL(cw.ServiceAreaOptionID, 0) ServiceAreaOptionID,
               cw.ServiceAreaOptionName,
               cw.ServiceAreaOptionDescription,
               ISNULL(cw.BidYearRenewalTypeID, 0) BidYearRenewalTypeID,
               ISNULL(cw.BidYearPlanInfoID, 0) BidYearPlanInfoID,
               ISNULL(cw.CurrentYearPlanInfoID, 0) CurrentYearPlanInfoID,
               ISNULL(cw.IsActive, 0) IsActive,
               ISNULL(rfi.PlanInfoID, 0) RollupPlanInfoID,
               rfi.ServiceAreaOptionID RollupSAOptionID,
               rfi.RollupName,
               ISNULL(tpfMRA.[date], 0) AS MRALastUpdatedTime,
               ISNULL(tpfMarketAdj.[date], 0) AS MarketAdjLastUpdatedTime,
               ISNULL(tmpCalcSign.PlanInfoID, 0) CalcPlanInfoID,
               ISNULL(tmpCalcSign.ServiceAreaOptionID, 0) CalcServiceAreaOptionID,
               ISNULL(tmpCalcSign.BasePlanInfoID, 0) CalcBasePlanInfoID,
               ISNULL(tmpCalcSign.Status, '') [Status],
               ISNULL(tmpCalcSign.RemovedMM, 0.0) RemovedMM,
               ISNULL(tmpCalcSign.TotalMM, 0.0) TotalMM,
               ISNULL(tmpCalcSign.MovingMM, 0.0) MovingMM,
               ISNULL(tmpCalcSign.SignificancePct, 0.0) SignificancePct,
               ISNULL(tmpCalcSign.IsWks1BasePlan, 0) IsWks1BasePlan,
               @BidYear AS BidYear

        --tmpCalcSign.Status,tmpCalcSign.RemovedMM,tmpCalcSign.TotalMM,tmpCalcSign.SignificancePct,tmpCalcSign.IsWks1BasePlan
        FROM vwPlanInfo pl WITH (NOLOCK)
            LEFT JOIN
            (
                SELECT DISTINCT
                       BidYear,
                       SSStateCountyCD,
                       BaseYearCPS,
                       CurrentYearCPS,
                       BidYearCPS,
                       ServiceAreaOptionID,
                       ServiceAreaOptionName,
                       ServiceAreaOptionDescription,
                       BidYearRenewalTypeID,
                       BidYearPlanInfoID,
                       CurrentYearPlanInfoID,
                       IsActive
                FROM vwSAMCrosswalks WITH (NOLOCK)
            ) cw
                ON pl.PlanInfoID = cw.BidYearPlanInfoID
                   AND pl.PlanYear = cw.BidYear
            LEFT JOIN
            (
                SELECT DISTINCT
                       PlanInfoID,
                       ServiceAreaOptionID,
                       RollupName
                FROM vwRollupForecastInfo WITH (NOLOCK)
            ) rfi
                ON pl.PlanInfoID = rfi.PlanInfoID
            LEFT JOIN #tmpTPFMRA tpfMRA WITH (NOLOCK)
                ON pl.PlanYear = tpfMRA.PlanYearID
                   AND pl.CPS = tpfMRA.BidYearCPBPS
            LEFT JOIN #tmpTPFMarketAdj tpfMarketAdj WITH (NOLOCK)
                ON pl.CPS = tpfMarketAdj.BidYearCPBPS
            LEFT JOIN #tmpCalcSign tmpCalcSign WITH (NOLOCK)
                ON pl.PlanInfoID = tmpCalcSign.PlanInfoID
        WHERE pl.PlanYear >
        (
            SELECT MIN(PlanYear) - 3
            FROM LkpPlanYear WITH (NOLOCK)
            WHERE IsProjectedYear = 1
        )
              AND
              (
                  (@DivisionList IS NULL)
                  OR pl.Division IN
                     (
                         SELECT Value FROM dbo.fnStringSplit(@DivisionList, ',')
                     )
              )
              AND
              (
                  (@RegionList IS NULL)
                  OR pl.Region IN
                     (
                         SELECT Value FROM dbo.fnStringSplit(@RegionList, ',')
                     )
              )
              AND
              (
                  (@PlanIDList IS NULL)
                  OR pl.PlanInfoID IN
                     (
                         SELECT DISTINCT Value FROM dbo.fnStringSplit(@PlanIDList, ',')
                     )
              );

    END;

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    DECLARE @ErrorException NVARCHAR(4000);
    DECLARE @errSrc VARCHAR(MAX) = ISNULL(ERROR_PROCEDURE(), 'SQL'),
            @currentdate DATETIME = GETDATE();

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE(),
           @ErrorException
               = N'Line Number :' + CAST(ERROR_LINE() AS VARCHAR) + N' .Error Severity :'
                 + CAST(@ErrorSeverity AS VARCHAR) + N' .Error State :' + CAST(@ErrorState AS VARCHAR);
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    ---Insert into app log for logging error------------------  
    EXEC spAppAddLogEntry @currentdate,
                          '',
                          'ERROR',
                          @errSrc,
                          @ErrorMessage,
                          @ErrorException,
                          @LastUpdateByID;
    ;

        END CATCH;
    END;

标签： sql-server-2008ssms

解决方案

这些变量表示以逗号分隔的列表（将列表传递给查询的一种基本方式），然后查询仅在列表中有任何内容时才进行过滤：

              (
                  (@DivisionList IS NULL)  -- either the list is empty
                  OR pl.Division IN
                     (
                         SELECT Value FROM dbo.fnStringSplit(@DivisionList, ',')
                         -- this seems to be a table function which splits the string by commas
                         -- the result of the function is like another view or table
                     )
              )

所以要么列表为空，要么列表中有匹配项。

没有来源fnStringSplit我不能多说，但很可能它的性能并不好。在较新版本的 SQL Server 中，您可以STRING_SPLIT改用它，它的性能应该更好。

更好的选择是传入一个实际的表值参数，这本质上是一个只读表变量。

然后代码如下：

              (
                  NOT EXISTS (SELECT 1 FROM @DivisionList)  -- either the list is empty
                  OR pl.Division IN
                     (
                         SELECT Value FROM @DivisionList
                         -- no need to split
                     )
              )

推荐阅读