本文共 2099 字，大约阅读时间需要 6 分钟。
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- [SelectBase] 1,1,'( select j.*,u.P_Name from Job j left join Users u on j.UserID=u.ID) T','Flag=0' alter procedure [ SelectBase ] @PageIndex int , @PageSize int , @TableName nvarchar ( 2000 ), @Where nvarchar ( 2000 ) = '' as Declare @rowcount int Declare @intStart int Declare @intEnd int declare @Column1 varchar ( 32 ) -- 第一列名称 Declare @SQl nvarchar ( 2000 ), @WhereR nvarchar ( 1000 ), @OrderBy nvarchar ( 1000 ) set @rowcount = 0 set nocount on if @Where <> '' begin set @Where = ' and ' + @Where end if CHARINDEX ( ' order by ' , @Where ) > 0 begin set @WhereR = substring ( @Where , 1 , CHARINDEX ( ' order by ' , @Where ) - 1 ) -- 取得条件 set @OrderBy = substring ( @Where , CHARINDEX ( ' order by ' , @Where ), Len ( @Where )) -- 取得排序方式(order by 字段 方式) end else begin set @WhereR = @Where set @OrderBy = ' order by id asc ' end set @SQl = ' SELECT @rowcount=count(*) from ' + cast ( @TableName as nvarchar ( 2000 )) + ' where 1=1 ' + @WhereR exec sp_executeSql @SQl ,N ' @rowcount int output ' , @rowcount output if @PageIndex = 0 and @PageSize = 0 -- 不进行分页,查询所有数据列表 begin set @SQl = ' SELECT * from ' + cast ( @TableName as nvarchar ( 2000 )) + ' where 1=1 ' + @Where end else -- 进行分页查询数据列表 begin set @intStart = ( @PageIndex - 1 ) * @PageSize + 1 ; set @intEnd = @intStart + @PageSize - 1 declare @PKName nvarchar ( 50 ) if ( len ( @TableName ) > 50 ) begin Set @Column1 = ' ID ' end else begin set @Column1 = col_name ( object_id ( @tableName ), 1 ) -- 设置第一列名称 end set @SQl = ' Create table #tem(Row int identity(1,1) not null,joinRow int) ' set @SQl = @SQl + ' insert #tem(joinRow) select ' + @Column1 + ' from ' + @TableName + ' where 1=1 ' + @WhereR set @SQl = @SQl + ' select * from ' + @TableName + ' right join #tem on ' + @Column1 + ' =#tem.joinRow ' set @SQl = @SQl + ' where #tem.Row between ' + cast ( @intStart as varchar ) + ' and ' + cast ( @intEnd as varchar ) end -- PRINT @SQl exec sp_executeSql @SQl return @rowcount set nocount off
版权声明：本文原创发表于博客园，作者为路过秋天，原文链接：http://www.cnblogs.com/cyq1162/archive/2009/08/13/1545028.html