sql - 如何用函数替换where子句并在存储过程中使用它？

Question

我有两个存储过程，它们具有相同的 where 子句，其中一个用于分页：

ALTER PROCEDURE [dbo].[ret_PayrollCalculations_GetPagedFilteredPersonnels]
(
    @SortField varchar(512),
    @PageNo int,
    @PageSize int,
    @CalculationCommandType int,
    @StartWorkingPeriodId int,
    @StartYear int,
    @EndWorkingPeriodId int,
    @EndYear int,
    @Status int,
    @SalariedGuid uniqueidentifier,
    @SalariedType int,
    @OfficeNumber varchar(64),
    @SalariedResultSetId int,
    @Keyword nvarchar(2024),
    @OperationalUnitIds [dbo].[ListOfID]  READONLY
)
 AS
DECLARE @AccessibleSalariedGuids [dbo].[ListOfGuid]
IF EXISTS (SELECT * FROM @OperationalUnitIDs)
BEGIN
    INSERT INTO @AccessibleSalariedGuids
    SELECT FeatureGuid FROM prs_OperationalUnitFeatures 
    WHERE 
        OperationalUnitID in (SELECT * FROM @OperationalUnitIDs) AND 
        FeatureFlag IN (2,4)
END 
ELSE BEGIN 
    INSERT INTO @AccessibleSalariedGuids
    SELECT [Guid] FROM ret_vwSalaried
END
DECLARE @OffsetRows INT = CASE WHEN @PageNo = 1 THEN 0 ELSE (@PageNo - 1) * @PageSize END;
DECLARE @TotalCount INT;
WITH Result AS(
    SELECT
        CASE
            WHEN @SortField = N'[FullName]' THEN ROW_NUMBER() OVER (ORDER BY salaried.[FullName])
            WHEN @SortField = N'[FullName] DESC' THEN ROW_NUMBER() OVER (ORDER BY salaried.[FullName] DESC)
            WHEN @SortField = N'[WorkingPeriodTitle]' THEN ROW_NUMBER() OVER (ORDER BY calcs.[Year],workingPeriods.[Index])
            WHEN @SortField = N'[WorkingPeriodTitle] DESC' THEN ROW_NUMBER() OVER (ORDER BY calcs.[Year] DESC,workingPeriods.[Index] DESC)
            WHEN @SortField = N'[PersonnelNo]' THEN ROW_NUMBER() OVER (ORDER BY salaried.[PersonnelNo])
            WHEN @SortField = N'[PersonnelNo] DESC' THEN ROW_NUMBER() OVER (ORDER BY salaried.[PersonnelNo] DESC)
        END AS [RowNumber],
        calcs.[Guid],
        calcs.[CalculationCommandGuid],
        calcs.[SalariedGuid],
        salaried.[PersonnelNo],
        salaried.[FullName] AS [PersonnelFullName],
        command.[Type] [CommandType],
        salaried.[SalariedType],
        workingPeriods.Title AS [WorkingPeriodTitle],
        command.[MainYear] AS [Year],
        command.[Approved],
        command.[FinalApproved]
    FROM
        ret_PayrollCalculationCommands command INNER JOIN
        ret_PayrollCalculations calcs ON calcs.[CalculationCommandGuid] = command.[Guid] INNER JOIN
        ret_vwSalaried salaried ON calcs.[SalariedGuid] = salaried.[Guid] INNER JOIN 
        prs_workingPeriods workingPeriods ON workingPeriods.[Id] = command.[MainWorkingPeriodID]
    WHERE 
        ISNULL(calcs.[MainCalculation],0) = 1 AND
        ISNULL(command.[Deleted],0)=0 AND
        (@Keyword = '' OR salaried.PersonnelNo = @Keyword OR salaried.FullName LIKE N'%' + @Keyword + '%' OR salaried.FullNameReversed LIKE N'%' + @Keyword + '%') AND
        (ISNULL(@calculationCommandType, 0) = 0 OR command.[Type] = @calculationCommandType) AND
        (ISNULL(@StartYear, 0) = 0 OR command.[MainYear] >= @StartYear) AND
        (ISNULL(@StartWorkingPeriodId, 0) = 0  OR command.[MainWorkingPeriodID] >= @StartWorkingPeriodId) AND
        (ISNULL(@EndYear, 0) = 0 OR command.[MainYear] <= @EndYear) AND
        (ISNULL(@EndWorkingPeriodId, 0) = 0 OR command.[MainWorkingPeriodID] <= @EndWorkingPeriodId) AND
        (ISNULL(@Status, -1) = -1 OR command.[Approved] = @Status) AND
        (ISNULL(@SalariedType, -1) = -1 OR salaried.[SalariedType] = @SalariedType) AND
        (ISNULL(@SalariedGuid,'00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' OR calcs.[SalariedGuid] = @SalariedGuid) AND
        (@OfficeNumber IS NULL OR salaried.[OfficeNumber] LIKE '%'+@OfficeNumber+'%') AND
        (ISNULL(@SalariedResultSetId, -1) = -1 OR calcs.[SalariedGuid] IN (SELECT [SalariedGuid] FROM ret_SalariedResultSetItems WHERE SalariedResultSetID = @SalariedResultSetId)) AND
        (calcs.[SalariedGuid] IN (SELECT * FROM @AccessibleSalariedGuids))
), TableForTotalCount AS (SELECT COUNT(*) As TotalCount FROM Result)
    SELECT 
        (SELECT TOP 1 TotalCount FROM TableForTotalCount) AS TotalCount,
        *
    FROM Result
    ORDER BY 
        [RowNumber]
        OFFSET @OffsetRows ROWS
FETCH NEXT @PageSize ROWS ONLY

另一个应该返回一些Guids

ALTER PROCEDURE [dbo].[ret_PayrollCalculations_GetFilteredPersonnels]
    (
    @CalculationCommandType int,
    @StartWorkingPeriodId int,
    @StartYear int,
    @EndWorkingPeriodId int,
    @EndYear int,
    @Status int,
    @SalariedGuid uniqueidentifier,
    @SalariedType int,
    @OfficeNumber varchar(64),
    @SalariedResultSetId int,
    @Keyword nvarchar(2024),
    @OperationalUnitIds [dbo].[ListOfID]  READONLY
)
 AS
DECLARE @AccessibleSalariedGuids [dbo].[ListOfGuid]
IF EXISTS (SELECT * FROM @OperationalUnitIDs)
BEGIN
    INSERT INTO @AccessibleSalariedGuids
    SELECT FeatureGuid FROM prs_OperationalUnitFeatures 
    WHERE 
        OperationalUnitID in (SELECT * FROM @OperationalUnitIDs) AND 
        FeatureFlag IN (2,4)
END 
ELSE BEGIN 
    INSERT INTO @AccessibleSalariedGuids
    SELECT [Guid] FROM ret_vwSalaried
END
    SELECT
        calcs.[Guid]
    FROM
        ret_PayrollCalculationCommands command INNER JOIN
        ret_PayrollCalculations calcs ON calcs.[CalculationCommandGuid] = command.[Guid] INNER JOIN
        ret_vwSalaried salaried ON calcs.[SalariedGuid] = salaried.[Guid]
    WHERE 
        ISNULL(calcs.[MainCalculation],0) = 1 AND
        ISNULL(command.[Deleted],0)=0 AND
        (@Keyword = '' OR salaried.PersonnelNo = @Keyword OR salaried.FullName LIKE N'%' + @Keyword + '%' OR salaried.FullNameReversed LIKE N'%' + @Keyword + '%') AND
        (ISNULL(@calculationCommandType, 0) = 0 OR command.[Type] = @calculationCommandType) AND
        (ISNULL(@StartYear, 0) = 0 OR command.[MainYear] >= @StartYear) AND
        (ISNULL(@StartWorkingPeriodId, 0) = 0  OR command.[MainWorkingPeriodID] >= @StartWorkingPeriodId) AND
        (ISNULL(@EndYear, 0) = 0 OR command.[MainYear] <= @EndYear) AND
        (ISNULL(@EndWorkingPeriodId, 0) = 0 OR command.[MainWorkingPeriodID] <= @EndWorkingPeriodId) AND
        (ISNULL(@Status, -1) = -1 OR command.[Approved] = @Status) AND
        (ISNULL(@SalariedType, -1) = -1 OR salaried.[SalariedType] = @SalariedType) AND
        (ISNULL(@SalariedGuid,'00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' OR calcs.[SalariedGuid] = @SalariedGuid) AND
        (@OfficeNumber IS NULL OR salaried.[OfficeNumber] LIKE '%'+@OfficeNumber+'%') AND
        (ISNULL(@SalariedResultSetId, -1) = -1 OR calcs.[SalariedGuid] IN (SELECT [SalariedGuid] FROM ret_SalariedResultSetItems WHERE SalariedResultSetID = @SalariedResultSetId)) AND
        (calcs.[SalariedGuid] IN (SELECT * FROM @AccessibleSalariedGuids))

当出现错误时，我必须修复两个存储过程中的问题，以避免重复我想要函数中的 Where 子句并在存储过程中调用函数，但我不知道如何？

标签： sqlsql-server

Answer 1

这就是我的处理方式：如果您使用的是 Microsoft sql-server，则可以制作 transact-sql 代码。

将您的 sql 过程转换为字符串，并使 Where 子句成为您在其他地方声明的文本变量。所以它正在创建一个元过程。

例如。

    DECLARE @whereClause LONGTEXT; 
    DECLARE @SQLString LONGTEXT;

    SET @whereClause = 'i=1'

    SET @SQLString = 'SELECT * FROM table WHERE' & @whereClause 
    sp_executesql SQLString