T-SQL Paging Sorting & Filtering - Filtering not Working

T-SQL Paging Sorting & Filtering

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

and

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

see below.

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

Can anyone please help?

Regards

Walter

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
   开发者_C百科 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.

Thanks again.

Walter.

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

Based on those requirements alone, this is what I would do:

WHERE ((@StartAlpha IS NULL) OR (LEFT(Title, 1) = @StartAlpha))

What do you get when you run just this part? (Please also state which values you are using in your parameters)

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 + '%'))  
)
SELECT * FROM [MyTempArea]

@Andreas LEFT(Title, 1) syntax would perform better than the like comparison... but using the like, this should work:

WHERE (Title Like isnull(@StartAlpha, '') + '%')

(The combination of working with nulls that OR makes me edgy.)