I'm fairly new to sql stored procedures. ok, I currently have a stored
procedure that dynamically sorts as well as pages through a recordset:
__________________________________________________ _________________________
PROCEDURE dbo.ViewAllSales
@Page int,
@RecsPerPage int,
@SortSQL varchar(100),
@varSession_ID varchar(4),
@varFirstDayMonth_Date datetime,
@varCurrentDayMonthOrder_Date datetime
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TempViewSales
(
SalesAutoID int IDENTITY,
TransID int
)
DECLARE @SearchSQL varchar(4000)
SELECT @SearchSQL = 'INSERT INTO #TempViewSales (TransID)' +
' SELECT Transaction_ID FROM tblTransactions WHERE Session_ID =
' + @varSession_ID +
' AND Order_Date >= ''' + Convert(varchar(25),
@varFirstDayMonth_Date, 121) + ''' AND Order_Date <= ''' +
Convert(varchar(25), @varCurrentDayMonthOrder_Date, 121) + '''AND
Active <> 0 ORDER BY ' + @SortSQL
EXECUTE(@SearchSQL)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT *, MoreRecords = (SELECT COUNT(*) FROM #TempViewSales WHERE
SalesAutoID >= @LastRec)
FROM #TempViewSales INNER JOIN tblTransactions ON
tblTransactions.Transaction_ID = #TempViewSales.TransID
WHERE SalesAutoID > @FirstRec AND SalesAutoID < @LastRec
SET NOCOUNT OFF
END
__________________________________________________ _________________________
How do i return a record count? And how would i implement a "page x of
y" sceanario? Thanks in advance.