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
