CREATE PROCEDURE
sp_GetOrders(@subset int = 0, @rowcount int = 10, @orderby varchar(100) = 'OrderDate')
AS
--Use the Northwind Database for this example
BEGIN
SET NOCOUNT ON
DECLARE @ABSPOS INT
SET @ABSPOS = @SUBSET * @ROWCOUNT + 1
CREATE TABLE
#ORDERS
(OrderDate DATETIME, ShipName VARCHAR(50), ShipCity VARCHAR(50))
DECLARE
@OrderDate DATETIME,
@ShipName VARCHAR(50),
@ShipCity VARCHAR(50)
EXEC('DECLARE cOrders SCROLL CURSOR FOR
SELECT OrderDate, ShipName, ShipCity FROM Orders ORDER BY ' + @orderby)
OPEN cOrders
DECLARE @COUNTER INT
SET @COUNTER = 0
FETCH ABSOLUTE @ABSPOS FROM cOrders INTO @OrderDate, @ShipName, @ShipCity
WHILE( @@FETCH_STATUS = 0 AND @COUNTER < @ROWCOUNT)
BEGIN
INSERT #ORDERS VALUES(@OrderDate, @ShipName, @ShipCity)
SET @COUNTER = @COUNTER + 1
FETCH FROM cOrders INTO @OrderDate, @ShipName, @ShipCity
END
CLOSE cOrders
DEALLOCATE cOrders
SELECT * FROM #ORDERS
SET NOCOUNT OFF
END
GO
|