Make your own free website on Tripod.com
Using Temporary Table to Divide the Results Set In More Pages [Home]

CREATE PROCEDURE spGetPage
@Search NVARCHAR (100),
@Page SMALLINT,
@RowsPerPage DECIMAL,
@NumberOfPages INT OUTPUT
AS

SET NOCOUNT ON
DECLARE @NumberOfRows DECIMAL

--tabelul temporar
CREATE TABLE #Temp (TempID INT IDENTITY (1, 1), [ID] INT, [Name] NCHAR (100), [Description] NVARCHAR (200))

INSERT INTO #Temp
SELECT [ID], [Name], [Description]
FROM Article
WHERE [Name] LIKE @Search + '%'
ORDER BY [Name]

--numărul de înregistrări
SELECT @NumberOfRows = COUNT (*) FROM #Temp

--calculez numărul de pagini
SET @NumberOfPages = CEILING (@NumberOfRows / @RowsPerPage)

--selectez înregistrările pentru pagina cerută
SELECT TempID, [ID], [Name], [Description]
FROM #Temp
WHERE TempID
BETWEEN ((@Page - 1) * @RowsPerPage + 1) AND ((@Page - 1) * @RowsPerPage + @RowsPerPage)

--șterg tabelul temporar
DROP TABLE #Temp
SET NOCOUNT OFF
RETURN @@ERROR
GO