加工入库单

exec P_Paged 'id',' select * from ','T_billprint','BatchNo desc',20,1,'kind =''加工入库单'''
------------------------------------
--
--用途：分页存储过程
--时间：2005年7月26日
--
-------------------------------------
create PROCEDURE P_Paged
    @firstField   varchar(100),  --表的唯一字段
    @selectfield   varchar(800), --其他字段,与@firstField无关,包含Select/From
    @tableName      varchar(100),       -- 字段及表名
    @orderfield   varchar(200),       -- 排序字段,包含desc,asc
    @PageSize     int = 20,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码   
    @strWhere     varchar(1000) = '1=1'  -- 查询条件 (注意: 不要加 where)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
declare @sql varchar (500)
declare @sql2 varchar (1000)
/*
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
*/
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    IndexId int IDENTITY (1, 1) NOT NULL,
    TableID int
)
-- Insert into the temp table
set @sql = 'INSERT INTO #PageIndex (TableID) SELECT ' + @firstField + ' FROM ' + @tableName + ' Where ' + @strWhere + ' order by ' + @orderfield

exec(@sql)

-- Return total count
SELECT COUNT(*) FROM #PageIndex

-- Return paged results
set @sql2 = @selectfield + @tableName + ' as t1 ,#PageIndex as tmp Where '
 + 't1.' + @firstfield + ' = tmp.TableID AND ' + ' tmp.IndexID > ' + str(@PageLowerBound) + ' AND tmp.IndexID < ' + str(@PageUpperBound)
exec(@sql2)
END
GO

CREATE TABLE
(
    IndexId int IDENTITY (1, 1) NOT NULL,
    TableID int
)
INSERT INTO #PageIndex (TableID)

SELECT * FROM #PageIndex --T_billprint   Where 1=1 order by id desc --' + @strWhere + ' order by ' + @orderfield