|
?
调用 带参数存储过程
declare @totalRec int
exec fenye 'venshop_hw',20,1,'hw_id desc','hw_id,hw_name,hw_sn,hw_price1,hw_price2,hw_kucun','sort_id=1',@totalRec output
print '总计'+convert(varchar(6),@totalRec)+'条'
?
?
/**
if? exists(select * from sysobjects where name='fenye')
drop proc fenye
**/
?
CREATE procedure fenye
? @tableName nvarchar(200),--表名
? @pageSize int,?? --每页显示条数
? @curPage int,? --当前页
? @orderBy nvarchar(200),?? --排序字段?
? @field nvarchar(200) = '*',? --要查询的字段
? @condition nvarchar(200),? --条件（不用写where）
? @recct int output?? --返回记录总数（输出参数）
AS
? SET NOCOUNT ON
? DECLARE
???? @STMT nvarchar(max)??????? -- SQL to execute
???? --@recct int????????????????? -- total # of records (for GridView paging interface)
? IF LTRIM(RTRIM(@condition)) = '' SET @condition = '1 = 1'
? IF @pageSize IS NULL BEGIN
??? SET @STMT =? 'SELECT?? ' + @field + 'FROM ' + @tableName +'WHERE??? ' + @condition + 'ORDER BY?? ' + @orderBy
??? EXEC (@STMT)???????????????? -- return requested records
? END ELSE BEGIN
??? SET @STMT =? 'SELECT?? @recct = COUNT(*) FROM???? ' + @tableName + '??? WHERE??? ' + @condition
??? EXEC sp_executeSQL @STMT,@params = N'@recct INT OUTPUT',@recct = @recct OUTPUT
??? --SELECT @recct AS recct?????? -- return the total # of records
??? DECLARE
????? @lbound int,
????? @ubound int
??? SET @curPage = ABS(@curPage)
??? SET @pageSize = ABS(@pageSize)
??? IF @curPage < 1 SET @curPage = 1
??? IF @pageSize < 1 SET @pageSize = 1
??? SET @lbound = ((@curPage - 1) * @pageSize)
??? SET @ubound = @lbound + @pageSize + 1
??? IF @lbound >= @recct BEGIN
????? SET @ubound = @recct + 1
????? SET @lbound = @ubound - (@pageSize + 1)
??? END
??? SET @STMT =? 'SELECT? ' + @field + '
????????????????? FROM??? (
??????????????????????????? SELECT? ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row,'+? @field +'
??????????????????????????? FROM??? ' + @tableName + '
??????????????????????????? WHERE?? ' + @condition + '
????????????????????????? ) AS tbl
????????????????? WHERE
????????????????????????? row > ' + CONVERT(varchar(9),@lbound) + ' AND
????????????????????????? row < ' + CONVERT(varchar(9),@ubound)
??? --EXEC? sp_executeSQL @STMT???????????????? -- return requested records
?EXEC? sp_executeSQL @STMT? END
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
