sql - 如何在存储过程中进行排序和过滤并优化性能？

Question

我想在我的存储过程中执行排序和过滤。

我为假期表创建表:

CREATE TABLE [dbo].[Holiday](
    [HolidaysId] [int] IDENTITY(1,1) NOT NULL,
    [HolidayDate] [date] NULL,
    [HolidayDiscription] [nvarchar](500) NULL,
    [Name] [nvarchar](max) NULL,
 CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED 
(
    [HolidaysId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

我的过滤标准如下:

1. 开头为
2. 等于
3. 不等于。

注意:过滤器比较中请忽略HolidayId。

我的 table :假期

HolidaysId int,Name nvarchar(500),HolidayDate date.

示例输入:

HolidayId       Name       Date
 1               abc       1/1/2015
 2               pqr       1/2/2015
 3               xyz       1/3/2015

输出:

Case 1:Starts with(This is just for name column only.likewise i want to do for HolidayDate column too)
Input:ab(filtering parameter)
Query:where Name like '%ab%' order by Name(when sort column name is  passed as parameter in stored procedure for column to sort(for eg:Name))
output:1,abc,1/1/2015

Case 2:Is Equal to(Same as above)
Input:prr(filtering parameter)
output:2,pqr,1/2/2015

Case 3:Not Equal to(Same as above)
Input:bbb(filtering parameter)
output:All Records

这是到目前为止我的存储过程:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_PagedItems]
    (
     @Page int,
     @RecsPerPage int
    )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    ID int,
    Name varchar(50),
    HolidayDate date
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ID, Name,HolidayDate)
SELECT HolidaysId,HolidayDiscription,HolidayDate FROM holiday 

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords = 
    (
     SELECT COUNT(*) 
     FROM #TempItems TI
     WHERE TI.ID >= @LastRec
    ) 
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

现在我要发送到我的存储过程的 4 件事是:

1. 页码
2. PageSize(要检索的记录数)
3. 对列名称(名称或假日日期)进行排序
4. 我的过滤器列名称(假日日期名称)和运算符，例如 StartWith 或等于或不等于。(ColumnName 和运算符)

任何人都可以帮助我执行排序和过滤吗？如果有任何与性能优化相关的更改，请建议我。

Answer 1

我还没有测试过这个，但是你可以使用类似的东西作为启动器并进行修改以使其稳定:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_PagedItems]
    (
    @ID int = NULL,
    @Name varchar(50) = NULL,
    @HolidayDate date = NULL,
    @SortCol varchar(20) = '',
     @Page int=1,
     @RecsPerPage int=10 -- default size, you can change it or apply while executing the SP
    )
AS
BEGIN
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    ID int,
    Name varchar(50),
    HolidayDate date
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ID, Name,HolidayDate)
SELECT HolidaysId, HolidayDiscription, HolidayDate 
FROM holiday 

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
; WITH CTE_Results
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @SortCol = 'ID_Asc' THEN ID
            END ASC,
        CASE WHEN @SortCol = 'ID_Desc' THEN ID
            END DESC, 

        CASE WHEN @SortCol = 'Name_Asc' THEN Name
            END ASC,
        CASE WHEN @SortCol = 'Name_Desc' THEN Name
            END DESC, 

        CASE WHEN @SortCol = 'HolidayDate_Asc' THEN HolidayDate
            END ASC,
        CASE WHEN @SortCol = 'HolidayDate_Desc' THEN HolidayDate
            END DESC
        ) AS ROWNUM,
        ID,
        Name,
        HolidayDate
    FROM #TempItems
    WHERE 
            (@ID IS NULL OR ID = @ID)
        AND (@Name IS NULL OR Name LIKE '%' + @Name + '%')
        AND (@HolidayDate IS NULL OR HolidayDate = @HolidayDate)
    )
SELECT 
        ID,
        Name,
        HolidayDate
    FROM CTE_Results
    WHERE
        ROWNUM > @FirstRec 
    AND ROWNUM < @LastRec
    ORDER BY ROWNUM ASC

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
END
GO

您可以查看我写过的博客文章:

1. Creating Stored Procedures with Dynamic Search (filter)

2. Creating Stored Procedures with Dynamic Search & Paging (Pagination)

3. Creating Stored Procedure with Dynamic Search, Paging and Sorting

4. 如果您使用的是 SQL 2012 或更高版本，您还可以使用 FETCH-OFFSET 子句进行分页，link .