ALTER procedure [dbo].[OverPage]
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@OrderField varchar(255), --排序的字段名
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWhere varchar(1500), --查询条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strOrder varchar(200) --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(50) --排序附加语句
---------------------排序------------------1是升序,0是降序
if @orderType!=0
begin
set @strtemp='>(select max('
set @strOrder=' order by '+@orderField+' asc'
end
else
begin
set @strtemp='<(select min('
set @strOrder=' order by '+@orderField+' desc'
end
---------------------总记录---------------------
if @strWhere!=''
begin
set @strSqlCount='select @Total=count(*) from '+@tbName+' where '+@strWhere
end
else
begin
set @strSqlCount='select @Total=count(*) from '+@tbName
end
---------------------如果是第1页---------------------
if @pageIndex=1
begin
if @strWhere!=''
begin
set @strSql='select top '+str(@pageSize)+@tbFields+ ' from '+@tbName+' where '+@strWhere+@strOrder
end
else
begin
set @strSql='select top '+str(@pageSize)+@tbFields+ ' from '+@tbName+@strOrder
end
end
---------------------如果不是第1页-------------------
else
begin
if @strWhere!=''
begin
set @strSql='select top '+str(@pageSize)+' '+@tbFields+' from '+@tbName+' where '+@OrderField+' '
+@strtemp+' '+@OrderField+' from (select top '+str((@pageIndex-1)*@pageSize)+
+@OrderField+' from '+@tbName+' where '+@strWhere+' '+@strOrder+')as tb)'+' '+@strOrder
end
else
begin
set @strSql='select top '+str(@pageSize)+' '+@tbFields+' from '+@tbName+' where '+@orderField+' '
+@strtemp+' '+@OrderField+' from (select top '+str((@pageIndex-1)*@pageSize)+' '
+@OrderField+' from '+@tbName+' '+@strOrder+')as tb)'+' '+@strOrder
end
end
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec (@strSql)
这是我用的一个分页存储过程,但是执行存储过程的时候会提示@Total未声明变量,明明声明了的,我把exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output这句注释掉就好了,但是我必须获得@Total的值,请问是哪错了?怎么改?
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@OrderField varchar(255), --排序的字段名
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWhere varchar(1500), --查询条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strOrder varchar(200) --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(50) --排序附加语句
---------------------排序------------------1是升序,0是降序
if @orderType!=0
begin
set @strtemp='>(select max('
set @strOrder=' order by '+@orderField+' asc'
end
else
begin
set @strtemp='<(select min('
set @strOrder=' order by '+@orderField+' desc'
end
---------------------总记录---------------------
if @strWhere!=''
begin
set @strSqlCount='select @Total=count(*) from '+@tbName+' where '+@strWhere
end
else
begin
set @strSqlCount='select @Total=count(*) from '+@tbName
end
---------------------如果是第1页---------------------
if @pageIndex=1
begin
if @strWhere!=''
begin
set @strSql='select top '+str(@pageSize)+@tbFields+ ' from '+@tbName+' where '+@strWhere+@strOrder
end
else
begin
set @strSql='select top '+str(@pageSize)+@tbFields+ ' from '+@tbName+@strOrder
end
end
---------------------如果不是第1页-------------------
else
begin
if @strWhere!=''
begin
set @strSql='select top '+str(@pageSize)+' '+@tbFields+' from '+@tbName+' where '+@OrderField+' '
+@strtemp+' '+@OrderField+' from (select top '+str((@pageIndex-1)*@pageSize)+
+@OrderField+' from '+@tbName+' where '+@strWhere+' '+@strOrder+')as tb)'+' '+@strOrder
end
else
begin
set @strSql='select top '+str(@pageSize)+' '+@tbFields+' from '+@tbName+' where '+@orderField+' '
+@strtemp+' '+@OrderField+' from (select top '+str((@pageIndex-1)*@pageSize)+' '
+@OrderField+' from '+@tbName+' '+@strOrder+')as tb)'+' '+@strOrder
end
end
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec (@strSql)
这是我用的一个分页存储过程,但是执行存储过程的时候会提示@Total未声明变量,明明声明了的,我把exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output这句注释掉就好了,但是我必须获得@Total的值,请问是哪错了?怎么改?
executesql @strSqlCount,N'@TotalCout int output',@Total output
EXEC SP_executesql @strSqlCount,N'@TotalCout int output',@Total output
declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @input_id int--定义需传入动态语句的参数的值 set @QueryString='select * from tablename where id=@id' --id为字段名,@id为要传入的参数
set @paramstring='@id int' --设置动态语句中参数的定义的字符串
set @input_id =1 --设置需传入动态语句的参数的值为1
exec sp_executesql @querystring,@paramstring,@id=@input_id
若有多个参数:
declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @input_id int--定义需传入动态语句的参数的值,参数1
declare @input_name varchar(20)--定义需传入动态语句的参数的值,参数2 set @QueryString='select * from tablename where id=@id and name=@name' --id与name为字段名,@id与@name为要传入的参数
set @paramstring='@id int,@name varchar(20)' --设置动态语句中参数的定义的字符串,多个参数用","隔开
set @input_id =1 --设置需传入动态语句的参数的值为1
set @input_name='张三' --设置需传入动态语句的参数的值为"张三"
exec sp_executesql @querystring,@paramstring,@id=@input_id,@name=@input_name --请注意参数的顺序
(2)输出参数
declare @num int, @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中?
declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @output_result int--查询结果赋给@output_result set @QueryString='select @totalcount=count(*) from tablename' --@totalcount 为输出结果参数
set @paramstring='@totalcount int output' --设置动态语句中参数的定义的字符串,多个参数用","隔开
exec sp_executesql @querystring,@paramstring,@totalcount=@output_result output
select @output_result
当然,输入与输出参数可以一起使用,大家可以自己去试一试。
另外,动态语句查询的结果集要输出的话,我只想到以下用临时表的方法,不知各位有没有更好的方法.
IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
drop table #tmp
select * into #tmp from tablename where 1=2 --创建临时表#tmp,其结构与tablename相同 declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
set @QueryString='select * from tablename '
insert into #tmp(field1,field2,...) exec(@querystirng)
@Total int output不能再DECLARE @Total INT了。。
DECLARE @COUNT INT
EXEC SP_EXECUTESQL N'SELECT @COUNT=COUNT(*) FROM NUM',N'@COUNT INT OUTPUT',@COUNT OUTPUT
SELECT @COUNT
-----------
12(所影响的行数为 1 行)
EXEC SP_executesql @strSqlCount,N' @Total int output',@Total@TotalCout output
EXEC SP_executesql @strSqlCount,N' @Total int output',@TotalCout output