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
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