-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE ContractRecord_GetAll_Page
-- Add the parameters for the stored procedure here
@filterExpression NVARCHAR(2000),
@sortExpression NVARCHAR(100),
@rowIndex INT = 0,
@pageSize INT = 24,
@TotalRecords INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #GoodsKind (RowNumber INT, ContractID uniqueidentifier)
IF ((@sortExpression IS NULL) OR (LEN(@sortExpression) = 0))
BEGIN
SET @sortExpression = 'ContractCode asc'
END
DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = N'
INSERT INTO #GoodsKind
SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNumber, ContractID
FROM vw_ContractRecord'
IF ((@filterExpression IS NOT NULL) AND (LEN(@filterExpression) > 0))
BEGIN
SET @SQLString = @SQLString + ' WHERE ' + @filterExpression
END
EXECUTE sp_executesql @SQLString
SELECT @TotalRecords = COUNT(ContractID)
FROM #GoodsKind
-- Insert statements for procedure here
SELECT p.ContractID,EntCode,ContractCode,GoodsLimitDepartment,Supplier,Merchandiser,ContractTime,
Remark,RealAmount,TaxAmount,RealAndTaxAmount,OperatorName,InputDate
FROM #GoodsKind as p INNER JOIN vw_ContractRecord as C on p.ContractID = C.ContractID
WHERE p.RowNumber BETWEEN @rowIndex + 1 AND @rowIndex + @PageSize ORDER BY p.RowNumber
END
GO
代码
