| 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