第一段是一个存储过程分页的代码CREATE proc getdataset
@TableList Varchar(200)='*',--搜索表的字段,比如:id,datatime,job,用逗号隔开
@TableName Varchar(30), --搜索的表名
@SelectWhere Varchar(500)='',--搜索条件,这里不用写where,比如:job='teacher' and class='2'
@SelectOrderId varchar(20),--表主键字段名。比如:id
@SelectOrder Varchar(200)='', --排序,可以使用多字段排序但主键字段必需在最前面.也可以不写,比如:order by class asc
@intPageNo int=1, --页号
@intPageSize int=10 ,--每页显示数
@RecordCount int OUTPUT --总记录数(存储过程输出参数)
as
declare @TmpSelect NVarchar(600)
declare @Tmp NVarchar(600) set nocount on--关闭计数set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' where '+@SelectWhereexecute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数
@RecordCount OUTPUT
if (@RecordCount = 0) --如果没有贴子,则返回零
return 0
/*判断页数是否正确*/
if (@intPageNo - 1) * @intPageSize > @RecordCount --页号大于总页数,返回错误
return (-1)
set nocount off--打开计数
if @SelectWhere != ''
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' where '+@SelectWhere +' '+@SelectOrder+') and '+@SelectWhere +' '+@SelectOrder
print @TmpSelect
end
else
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)--@@ROWCOUNT,返回受上一语句影响的行数。
GO请教的问题是:
execute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数
@RecordCount OUTPUT
这段代码中的N'@RecordCount int OUTPUT'是参数列表,@RecordCount OUTPUT是给参数赋值。这样理解对吗?第2段代码USE Factory;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(ExportDeptID)
FROM As_Inflexion
WHERE AcceptDeptID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
@SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 230;
EXECUTE sp_executesql
@SQLString --需要执行的SQL语句
,@ParmDefinition --参数列表
,@CustomerID = @IntVariable --给参数赋值
,@SalesOrderOUT =@SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;问题是,@SalesOrderOUT =@SalesOrderNumber OUTPUT;这句代码如果写成,@SalesOrderOUT OUTPUT 就会报错,
但是上面那段分页代码中写成@RecordCount OUTPUT却是正确的,请问是怎么回事啊?
@TableList Varchar(200)='*',--搜索表的字段,比如:id,datatime,job,用逗号隔开
@TableName Varchar(30), --搜索的表名
@SelectWhere Varchar(500)='',--搜索条件,这里不用写where,比如:job='teacher' and class='2'
@SelectOrderId varchar(20),--表主键字段名。比如:id
@SelectOrder Varchar(200)='', --排序,可以使用多字段排序但主键字段必需在最前面.也可以不写,比如:order by class asc
@intPageNo int=1, --页号
@intPageSize int=10 ,--每页显示数
@RecordCount int OUTPUT --总记录数(存储过程输出参数)
as
declare @TmpSelect NVarchar(600)
declare @Tmp NVarchar(600) set nocount on--关闭计数set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' where '+@SelectWhereexecute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数
@RecordCount OUTPUT
if (@RecordCount = 0) --如果没有贴子,则返回零
return 0
/*判断页数是否正确*/
if (@intPageNo - 1) * @intPageSize > @RecordCount --页号大于总页数,返回错误
return (-1)
set nocount off--打开计数
if @SelectWhere != ''
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' where '+@SelectWhere +' '+@SelectOrder+') and '+@SelectWhere +' '+@SelectOrder
print @TmpSelect
end
else
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)--@@ROWCOUNT,返回受上一语句影响的行数。
GO请教的问题是:
execute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数
@RecordCount OUTPUT
这段代码中的N'@RecordCount int OUTPUT'是参数列表,@RecordCount OUTPUT是给参数赋值。这样理解对吗?第2段代码USE Factory;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(ExportDeptID)
FROM As_Inflexion
WHERE AcceptDeptID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
@SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 230;
EXECUTE sp_executesql
@SQLString --需要执行的SQL语句
,@ParmDefinition --参数列表
,@CustomerID = @IntVariable --给参数赋值
,@SalesOrderOUT =@SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;问题是,@SalesOrderOUT =@SalesOrderNumber OUTPUT;这句代码如果写成,@SalesOrderOUT OUTPUT 就会报错,
但是上面那段分页代码中写成@RecordCount OUTPUT却是正确的,请问是怎么回事啊?
execute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数
@RecordCount OUTPUT
这段代码中的N'@RecordCount int OUTPUT'是参数列表,@RecordCount OUTPUT是给参数赋值。这样理解对吗?--对。问题是,@SalesOrderOUT =@SalesOrderNumber OUTPUT;这句代码如果写成,@SalesOrderOUT OUTPUT 就会报错,
但是上面那段分页代码中写成@RecordCount OUTPUT却是正确的,请问是怎么回事啊?
--可以写成:@SalesOrderNumber OUTPUT,后面那部份是赋值,@SalesOrderOUT 只是过程中的参数而已并不是赋值。
提示错误如下:
必须传递参数个数 4,并以 '@name = value' 的形式传递后续的参数。一旦使用了 '@name = value' 形式之后,所有后续的参数就必须以 '@name = value' 的形式传递。execute sp_executesql
@TmpSelect, N'@RecordCount int OUTPUT' ,
@RecordCount OUTPUT
请问这里N'@RecordCount int OUTPUT' 中的@RecordCount 指的是@TmpSelect中的@RecordCount ,
而@RecordCount OUTPUT中的@RecordCount指的是@RecordCount int OUTPUT --总记录数(存储过程输出参数)中的@RecordCount ,这2个@RecordCount是如何联系起来的啊?谢谢