执行存储过程了 如何显示执行结果出来的呢？
/*通用分页存储过程
参数:
@TableName = 数据表名
@PrimaryKey = 主键字段.一般是标识字段
@Fields = 要显示的字段列
@Condition = 查询的条件.不带"WHERE"
@OrderBy = 排序字段.不带"ORDER BY"
@Page = 当前显示的页码
@PageSize = 一页显示的记录数(页码大小)
@PageCount = 返回共有多少个页(页码总数)
@RecordCount = 返回共有多少条记录数
*/
ALTER PROCEDURE [dbo].[usp_PageDataReader_amyin]
(
@TableName nvarchar(250),
@PrimaryKey nvarchar(20),
@Fields nvarchar(2000)='*',
@Condition nvarchar(500)='',
@OrderBy nvarchar(350)='',
@Page int = 1 out,
@PageSize int = 20,
@PageCount int = 1 out,
@RecordCount int = 1 out
)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000),@PrimaryKeyType bit
IF(LEN(@Condition)>1)SET @Condition = ' WHERE ' + @Condition
IF(LEN(@OrderBy)>1)SET @OrderBy = ' ORDER BY '+ @OrderBy
IF(@Fields = '')SET @Fields = '*'
IF(@PageSize < 0)SET @PageSize = 20
--确认主键的排序方式
IF CHARINDEX(@PrimaryKey+' DESC',@OrderBy) > 0
SET @PrimaryKeyType = 0 --主键降序排序
ELSE
SET @PrimaryKeyType = 1 --主键升序排序
--计算总计录数
SET @sql = 'SELECT @RecordCount=COUNT(*) FROM ' + @TableName + @Condition
EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
--计算总页码数
--确定总页数
IF @RecordCount%@PageSize=0
SET @PageCount=CEILING(@RecordCount/@PageSize)
ELSE
SET @PageCount=CEILING(@RecordCount/@PageSize)+1
--计算当前页码
IF(@PageCount<1)SET @PageCount=1
IF(@Page < 1)SET @Page = 1
IF(@Page>@PageCount)SET @Page = @PageCount
IF(@Page = 1 OR @RecordCount < @PageSize)
BEGIN
--取第一页时直接SELECT
SET @sql = 'SELECT TOP ' + CAST(@PageSize AS Varchar(10)) + ' ' + @Fields + ' FROM ' + @TableName + @Condition + @OrderBy
END
ELSE
BEGIN
DECLARE @PrimaryKeySql Varchar(300),@MinMax varchar(20),@Index int
--处理主键中包括有[前缀]的字段
SET @MinMax = @PrimaryKey
SET @index = CharIndex('.',@MinMax)
IF(@index > 0)
SET @MinMax = SubString(@MinMax,@index + 1,LEN(@MinMax) - @index)
IF(@PrimaryKeyType = 0)
--主键降序排列
SET @PrimaryKeySql= @PrimaryKey + '<(SELECT MIN([' + @MinMax + '])'
ELSE
--主键升序排列
SET @PrimaryKeySql= @PrimaryKey + ' >(SELECT Max([' + @MinMax + '])'
IF(LEN(@Condition)>1)
SET @PrimaryKeySql = @Condition + ' AND ' + @PrimaryKeySql
ELSE
SET @PrimaryKeySql = ' WHERE ' + @PrimaryKeySql
--取其它页码
--SET @sql = 'SELECT TOP ' + CAST(@PageSize AS Varchar(10)) + ' ' + @Fields + ' FROM ' + @TableName + @PrimaryKeySql + ' FROM
--(SELECT TOP ' + CAST((@Page-1)*@PageSize AS Varchar(20)) + ' ' + @Fields + ' FROM ' + @TableName + @Condition + @OrderBy + ') AS tbtmp)' + @OrderBy
set @sql='select '+@Fields+' from (select '+@Fields+',row_number() over('+@OrderBy+') as pageid
from '+@TableName+@Condition+') as pagetable where pagetable.pageid between '+CAST((@Page-1)*@PageSize+1 AS Varchar(20)) +' and '+CAST(@Page*@PageSize AS Varchar(10))+''
Print @sql
END
--SELECT @sql
Exec(@sql)
exec usp_PageDataReader_amyin 'HomesInfo表','Id','*','','Id DESC' 执行存储过程了 如何显示执行结果出来的呢？
老是显示命令已成功完成。
