IF OBJECT_ID('SPS_PAGEMAKER') IS NOT NULL
DROP PROCEDURE SPS_PAGEMAKER
GO
CREATE PROCEDURE SPS_PAGEMAKER
@ID NVARCHAR(50),
@TABLE NVARCHAR(50),
@ROWSxPAGE INT,
@CURRENTPAGE INT,
@FIELDS NVARCHAR(1000)
AS
BEGIN
/*
SUMMARY: SCRIPT PAGINADOR
CALL: SPS_PAGEMAKER('IDBHTransaction', 'BHTransaction', 5, 1)
MADE: AGO 18, 2011
EXAMPLE CODE:
DECLARE
@ID NVARCHAR(50),
@TABLE NVARCHAR(50),
@ROWSxPAGE INT,
@CURRENTPAGE INT,
@FIELDS NVARCHAR(1000)
SET @ID = 'IDBHTransaction'
SET @TABLE = 'BHTransaction'
SET @ROWSxPAGE = 5
SET @CURRENTPAGE = 2
SET @FIELDS = 'IDBHTransaction, f0, f2, f3, f4'
*/
--DYNAMIC SQL (SET @ROWS)
DECLARE @SQL NVARCHAR(1000)
DECLARE @PARAM NVARCHAR(1000)
DECLARE @pTABLE NVARCHAR(50)
DECLARE @ROWS INT
SET @SQL = N'SELECT @pROWS = COUNT(@pID) FROM ' + @TABLE;
SET @PARAM = N'@pID VARCHAR(25), @TABLE VARCHAR(25), @pROWS INT OUTPUT';
EXECUTE sp_executesql @SQL, @PARAM, @pID = @ID, @TABLE = @pTABLE, @pROWS = @ROWS OUTPUT;
--/DYNAMIC SQL
DECLARE @REST INT
DECLARE @PAGES INT
SET @REST = (SELECT @ROWS % @ROWSxPAGE)
SET @PAGES = (SELECT @ROWS / @ROWSxPAGE) IF (@REST > 0) SET @PAGES = @PAGES+1
--SELECT @ROWS AS ROWS, @ROWSxPAGE AS ROWSxPAGE, @PAGES AS PAGES, @REST AS REST
IF @CURRENTPAGE = 1
BEGIN
SET @SQL = N'SELECT ' + @FIELDS +
' FROM ' + @TABLE +
' WHERE ((' + @ID + ' >= 1) AND (' + @ID + ' < (1 + ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ')))';
SET @PARAM = N'@TABLE VARCHAR(25)';
EXECUTE sp_executesql @SQL, @PARAM, @TABLE = @pTABLE;
END
ELSE
IF (@CURRENTPAGE = @PAGES) AND (@REST > 0)
BEGIN
SET @SQL = N'SELECT ' + @FIELDS +
' FROM ' + @TABLE +
' WHERE (' + @ID + ' >= ((' + CONVERT(NVARCHAR(5),@CURRENTPAGE) + ' - 1) * ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ') + 1)';
SET @PARAM = N'@TABLE VARCHAR(25)';
EXECUTE sp_executesql @SQL, @PARAM, @TABLE = @pTABLE;
END
ELSE
BEGIN
SET @SQL = N'SELECT ' + @FIELDS +
' FROM ' + @TABLE +
' WHERE ((' + @ID + ' >= (((' + CONVERT(NVARCHAR(5),@CURRENTPAGE) + ' - 1) * ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ') + 1)) AND '+
'(' + @ID + ' < ((((' + CONVERT(NVARCHAR(5),@CURRENTPAGE) + ' - 1) * ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ') + 1) + ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ')))';
SET @PARAM = N'@TABLE VARCHAR(25)';
EXECUTE sp_executesql @SQL, @PARAM, @TABLE = @pTABLE;
END
END
GO
