exec usp_GetListByPage 'cms_article.id','cms_article inner join cms_catalog on
cms_catalog.id=cms_article.catalogid','cms_article.id,cms_article.topic,cms_article.userid,cms_catalog.catalogname','cms_art
icle.vdatetime desc','',2,100
CREATE proc usp_GetListByPage
(
@PKs nvarchar(100),
@Tables nvarchar(100),
@Fields nvarchar(500),
@Sort nvarchar(100),
@Where nvarchar(4000),
@CurrentPage int,
@PageSize int,
@RecordCount int=0 out
)
as
declare @SQL nvarchar(4000)
declare @SQLCount nvarchar(2000)
declare @Count int
select @SQL = 'select top ' + convert(nvarchar,@PageSize) + ' '+@Fields+' from ' + @Tables + ' where 1=1 '
if(rtrim(ltrim(@Where))!='')
begin
select @SQL = @SQL + ' and '+ @Where
end
select @SQL = @SQL +' and '+ @PKs +' not in'
select @SQL = @SQL + '(select top '+ convert(nvarchar,(@CurrentPage-1)*@PageSize) + ' ' + @PKs + ' from '+ @Tables +' where
1=1'
if(rtrim(ltrim(@Where))!='')
begin
select @SQL = @SQL + ' and ' + @Where
end
--select @SQL = @SQL + ')'
if(rtrim(ltrim(@Sort))!='')
begin
select @SQL = @SQL + ' order by ' + @Sort + ')'
select @SQL = @SQL + ' order by ' + @Sort
end
select @SQLCount = 'select @RecordCount= count(*) from ' + @Tables + ' where 1=1'
if(rtrim(ltrim(@Where))!='')
begin
select @SQLCount = @SQLCount + ' and ' + @Where
end
print @SQLCount
EXEC sp_executesql @SQLCount,N'@RecordCount int out',@RecordCount out
print @RecordCount
--select @RecordCount = exec(@SQLCount)
print @SQL
exec(@SQL)
GO
