Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to set out paramater when paging with t-sql and ROW_NUMBER() Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 10:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
Points: 501, Visits: 251
Is there a way that I can set the output parameter when I've got my t-sql written the way that I do, and if now whats the recommendation?

The ,@Total = COUNT(*) in the follow code does not work. How can I set the output parameter for paging?


USE [GenericCatalog]
GO
/****** Object: StoredProcedure [Generic].[proc_GetPartsForUserByCategory] Script Date: 8/27/2013 12:19:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [Generic].[proc_GetPartsForUserByCategory]
@UserID UNIQUEIDENTIFIER,
@GenericCatalogID INT,
@CategoryID INT,
@StartIndex INT,
@PageSize INT,
@Total INT OUT
AS

SET NOCOUNT ON;
SET @StartIndex = @StartIndex + 1

BEGIN
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNum, COUNT(*) OVER() AS Total
FROM (
SELECT p.*,gc.id'GenericCatalogID',@Total = COUNT(*),
gc.SupplierName,gc.SupplierEmail,gc.SupplierPhone,
pr.[Profile],pr.Siteline,pr.Depth

FROM [Generic].[Part] p WITH(NOLOCK) JOIN
Generic.UserPart up WITH(NOLOCK) ON up.PartID = p.ID JOIN
Generic.GenericCatalog gc WITH(NOLOCK) ON gc.ID = up.GenericCatID JOIN
Generic.[ProfileS] pr WITH(NOLOCK) ON p.ProfileID = pr.ID

WHERE p.ID = up.PartID
AND
CategoryID = @CategoryID
AND
gc.UserID = @UserID
AND gc.ID = @GenericCatalogID

) AS firstt
) AS final
WHERE RowNum BETWEEN @StartIndex AND (@StartIndex + @pageSize) - 1

ORDER BY final.Number ASC;


END;
SET NOCOUNT OFF;



Dam again!
Post #1489318
Posted Wednesday, August 28, 2013 12:50 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 4,351, Visits: 6,165
To my knowledge as long as you try to do something like this:

USE AdventureWorks2012
GO
DECLARE @totalrows int

SELECT *, @totalrows = TotalRows
FROM (
SELECT JobTitle,
BusinessEntityID,
ROW_NUMBER() OVER (ORDER BY BusinessEntityID) as RowNum,
COUNT(*) OVER () as TotalRows ---- nice to know how many total rows when paging to know how many pages are there
FROM HumanResources.Employee) AS t
WHERE RowNum BETWEEN 5 AND 15

you are going to get an error like this:

Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I can't think of a way off the top of my head to get the total EXPECTED rows while also FILTERING to a restricted set for output in a single pass. You store the data and use @@ROWCOUNT and then SELECT final set out (possibly using just key values to make a smaller temp objects - I have used that to GREAT effect in paging scenarios at clients over the years) or you do one hit as a count to set the output parameter and a second to get the data. With good indexing it can still be "fast", but clearly not as fast as if you could get the total with the restricted set...


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1489380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse