August 28, 2013 at 10:33 am
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]
@user-id 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 = @user-id
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!
August 28, 2013 at 12:50 pm
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 on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply