/*
Descript:单页数据存储过程
Author:XQF222
Date:10:38 2007-7-31
*/
CREATE PROCEDURE SP_SinglePageData(
@TableName nvarchar(200), ----要显示的表或多个表的连接
@SelectFields nvarchar(200) = '*', ----要显示的字段列表
@PrimaryFiled nvarchar(50), ----主表的主键
@SortField nvarchar(100) = null, ----排序字段列表或条件
@SortOrder bit = 0, ----排序方法，0为升序，1为降序
@SelectCondition nvarchar(200) = null, ----不带where字符串的查询条件
@TotalCounts int output ----返回查询到的记录总数
)
AS
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @sqlSort nvarchar(200) ----存放临时生成的排序条件
--生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @SelectCondition is null --没有设置显示条件
begin
set @sqlTmp = 'select ' + @SelectFields + ' From ' + @TableName
set @strTmp = 'select @TotalCounts=Count(' + @PrimaryFiled + ') FROM '+@TableName
end
else
begin
set @sqlTmp = 'select ' + @SelectFields + ' From ' + @TableName + ' where ' +
@SelectCondition
set @strTmp = 'select @TotalCounts=Count(' + @PrimaryFiled + ') FROM '+@TableName + '
where ' + @SelectCondition
end
--取得查询结果总数量-----
exec sp_executesql @strTmp,N'@TotalCounts int out ',@TotalCounts out
--print @TotalCounts
--生成排序字符串
if @SortOrder=0 --升序
begin
if not(@SortField is null)
set @sqlSort = ' Order by ' + @SortField
else
set @sqlSort = ' Order by ' + @PrimaryFiled
end
else --降序
begin
if not(@SortField is null)
set @sqlSort = ' Order by ' + @SortField + ' DESC'
else
set @sqlSort = ' Order by ' + @PrimaryFiled + ' DESC '
end
--生成最终查询字符串
if @sqlSort is null
set @sqlTmp = @sqlTmp
else
set @sqlTmp = @sqlTmp + @sqlSort
--print @sqlTmp
exec sp_executesql @sqlTmp
GO