T-SQL分页排序和过滤-过滤不起作用 [英] T-SQL Paging Sorting & Filtering - Filtering not Working

问题描述

T-SQL分页排序和过滤

T-SQL Paging Sorting & Filtering

现在我已经在T-SQL存储过程上工作了几个小时，这使我能够检索基于指定列以ASC或DESC顺序排序的一组分页的文章.

I have been working on a T-SQL stored procedure for a number of hours now that will enable me to retrieve a paged set of articles that are sorted in ASC or DESC order based on a specified column.

我现在正在努力使存储过程能够根据标题"字段的第一个字符进行过滤，并添加了以下行:

I am now working on getting the stored procedure to filter based on the first character of the 'Title' field and have added the lines:

@StartAlpha nvarchar(1) = null

和

WHERE ((@StartAlpha IS NULL) OR (Title Like @StartAlpha + '%'))

请参阅下文.

存储过程不再返回任何结果.而且我真的不知道为什么.

The stored procedure no longer returns any results. And I don't really know why.

任何人都可以帮忙吗?

致谢

沃尔特

USE [ABC]
GO
/****** Object:  StoredProcedure [dbo].[Get_MyArticles_Paged]    Script Date: 08/07/2011 20:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Get_MyArticles_Paged]

    /*Paging Total For Output*/
    @Row_Count BIGINT OUT,

    /*Paging Inputs*/
    @Page_Size INT = 10,
    @Page_Number INT = 1,

    @Sort_Column VARCHAR(100),  /* ('articleid','createdate','title','subject') */
    @Sort_Direction VARCHAR(4), /* ('ASC','DESC') */

    @StartAlpha nvarchar(1) = null

AS

BEGIN
print @StartAlpha
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    /*========================================================================
    Declare local variables
    ========================================================================*/
    DECLARE @FirstRecord int
    DECLARE @LastRecord int

    -- create a temporary space for paged result set
    DECLARE @PagedResults AS TABLE (
        [ArticleID] INT,
        [CreateDate] SMALLDATETIME,
        [Title] VARCHAR(200),
        [Subject] VARCHAR(500),
        [Row_Number] BIGINT,
        [Row_Count] BIGINT
    );

    /*========================
    Normalize Paging Parameters
    ==========================*/
    --Fix invalid input for Page Size
    SET @Page_Size = CASE 
        WHEN @Page_Size IS NULL THEN 10
        WHEN @Page_Size < 1 THEN 10
        ELSE @Page_Size
    END;

    --Fix invalid input for Page Number
    SET @Page_Number = CASE
        WHEN @Page_Number IS NULL THEN 1
        WHEN @Page_Number < 1 THEN 1
        ELSE @Page_Number
    END;

    --starting record to use.
    SET @FirstRecord = ((@Page_Number - 1) * @Page_Size) + 1

    --last record to use.
    SET @LastRecord = @FirstRecord + @Page_Size - 1

    --ensure sort column is valid in the list
    SET @Sort_Column = CASE
        WHEN LOWER(@Sort_Column) IN ('articleid','createdate','title','subject')
                THEN LOWER(@Sort_Column)
            ELSE
                'title' --default
    END

    --ensure sort direction is ASC or DESC
    SET @Sort_Direction = CASE
        WHEN LEFT(UPPER(COALESCE(@Sort_Direction, '')) + '    ', 4) = 'DESC' 
            THEN 'DESC' --explicit descending
        WHEN @Sort_Column = 'created' AND LEFT(UPPER(COALESCE(@Sort_Direction,'')) + '   ', 3) <> 'ASC' THEN
            'DESC' --default for created date
        ELSE 'ASC' --default otherwise
    END;

    /*============
    Prepare Results
    ==============*/
    WITH [MyTempArea] AS (
    SELECT TOP (@LastRecord)
            [ArticleID],
            [CreateDate],
            [Title],
            [Subject],
            ROW_NUMBER() OVER (
                ORDER BY
                    CASE WHEN(@Sort_Direction = 'ASC') THEN CASE WHEN @Sort_Column='articleid'      THEN [articleid] END END ASC,
                    CASE WHEN(@Sort_Direction = 'ASC') THEN CASE WHEN @Sort_Column='createdate'     THEN [createdate] END END ASC,
                    CASE WHEN(@Sort_Direction = 'ASC') THEN CASE WHEN @Sort_Column='title'          THEN [title] END END ASC,
                    CASE WHEN(@Sort_Direction = 'ASC') THEN CASE WHEN @Sort_Column='subject'    THEN [subject] END END ASC,
                    CASE WHEN(@Sort_Direction = 'DESC') THEN CASE WHEN @Sort_Column='articleid'     THEN [articleid] END END DESC,
                    CASE WHEN(@Sort_Direction = 'DESC') THEN CASE WHEN @Sort_Column='createdate'        THEN [createdate] END END DESC,
                    CASE WHEN(@Sort_Direction = 'DESC') THEN CASE WHEN @Sort_Column='title'         THEN [title] END END DESC,
                    CASE WHEN(@Sort_Direction = 'DESC') THEN CASE WHEN @Sort_Column='subject'   THEN [subject] END END DESC
            ) AS [Row_Number],
            COUNT(*) OVER () AS [Row_Count]
        FROM Articles
        WHERE ((@StartAlpha IS NULL) OR (Title Like @StartAlpha + '%'))  
    )

    INSERT INTO @PagedResults
    SELECT * FROM [MyTempArea] WHERE [Row_Number] >= @FirstRecord;

    /*===========
    Return Results
    =============*/
    -- @Row_Count output param
    SELECT @Row_Count = COALESCE(MAX(Row_Count), 0) FROM @PagedResults;

    -- Paged results set to return
    SELECT [ArticleID],[CreateDate],[Title],[Subject]
        FROM @PagedResults
    ORDER BY [Row_Number];

END

推荐答案

感谢所有提出有用建议的人.

Thanks to everyone that made helpful suggestions.

我完全重构了存储过程，现在可以正常工作了.见下文.

I completely refactored the stored procedure and it now works. See below.

我不确定最初的存储过程为什么不起作用以及此版本为什么不能起作用，但我想我会与论坛分享.

I'm not entirely sure why the original Stored Procedure didn't work and why this version does, but I thought I would share with the forum.

再次感谢.

沃尔特.

ALTER PROCEDURE [dbo].[Account_ContactGetData]
    @CurrentPage int = null,  
    @PageSize int = null,  
    @SortColumn nvarchar(max) = null,
    @SortDirection varchar(5),
    @StartAlpha nvarchar(1) = null

WITH EXECUTE AS CALLER
AS
BEGIN

SET NOCOUNT ON;

    DECLARE @FirstRecord int;
        DECLARE @LastRecord int;

    --starting record to use.
    SET @FirstRecord = ((@CurrentPage - 1) * @PageSize) + 1;

    --last record to use.
    SET @LastRecord = @FirstRecord + @PageSize - 1;

with ContactCTE as
(
    SELECT [ContactID], [DisplayName], [FirstName], [MiddleName], [LastName],    
    (ROW_NUMBER() OVER (Order By

    CASE WHEN @SortColumn='ContactID' AND @SortDirection='DESC' THEN ContactID END DESC,
        CASE WHEN @SortColumn='ContactID' AND @SortDirection='ASC' THEN ContactID END ASC,

        CASE WHEN @SortColumn='DisplayName' AND @SortDirection='DESC' THEN DisplayName END DESC,
        CASE WHEN @SortColumn='DisplayName' AND @SortDirection='ASC' THEN DisplayName END ASC,

        CASE WHEN @SortColumn='FirstName' AND @SortDirection='DESC' THEN FirstName END DESC,
        CASE WHEN @SortColumn='FirstName' AND @SortDirection='ASC' THEN FirstName END ASC,

        CASE WHEN @SortColumn='MiddleName' AND @SortDirection='DESC' THEN MiddleName END DESC,
        CASE WHEN @SortColumn='MiddleName' AND @SortDirection='ASC' THEN MiddleName END ASC,

    CASE WHEN @SortColumn='LastName' AND @SortDirection='DESC' THEN LastName END DESC,
        CASE WHEN @SortColumn='LastName' AND @SortDirection='ASC' THEN LastName END ASC 

    )) AS Row
    FROM Contact
    WHERE
    ((@StartAlpha is NULL) OR (LastName Like @StartAlpha+ '%'))  
)   
SELECT [ContactID], [DisplayName], [FirstName], [MiddleName], [LastName]
    FROM ContactCTE
WHERE Row BETWEEN @FirstRecord AND @LastRecord  

END

这篇关于T-SQL分页排序和过滤-过滤不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！