DECLARE @start Varchar(10), @S Nvarchar(300), @TableName Varchar(100)
SET @S = 'Select @start=UserID From ' + @TableName + ' Order By UserID'
EXEC sp_executesql @S, N'@start Varchar(10) output', @start output
SET @S = 'Select @start=UserID From ' + @TableName + ' Order By UserID'
EXEC sp_executesql @S, N'@start Varchar(10) output', @start output
Declare @start int,TableName varchar(255)
Declare @S Nvarchar(300)
SET @S = 'Select @start=UserID From ' + @TableName + ' Order By UserID'
EXEC sp_executesql @S, N'@start int output', @start output
Execute sp_execute @sql,N'@start int Output',@start Output服务器: 消息 214,级别 16,状态 2,过程 sp_execute,行 31
过程需要参数 '@handle' 为 'int' 类型。不行呢
--表名称,页数,每页记录数,显示字段,排序字段,条件
(@TableName varchar(255),@PageNum int,@PageSize int,@ShowFields varchar(1986),@OrderFields varchar(255),@WhereStr varchar(255))AsSet Nocount On
Begin
If @PageNum<=1 --页数为1或小于1
Begin
Set RowCount @PageSize --类似于Top
Execute('Select ' + @ShowFields + ' From SZY_User Where (' + @WhereStr + ') Order By ' + @OrderFields)
End
Else
Begin
Declare @start int,@end int,@Sum int,@i int,@sql nvarchar(4000)
Set @sql=N'Select @Sum=Count(*) From [' + @TableName + '] Where ' + @WhereStr
Execute sp_executesql @sql,N'@Sum int',@Sum --获得总记录数
If @PageNum>Ceiling(Convert(Decimal,@Sum)/@PageSize)
Begin
Set @PageNum=Ceiling(Convert(Decimal,@Sum)/@PageSize) --末页
End
Set @i=@PageSize*(@PageNum-1)
Set RowCount @i 就是这一行有错误:Set @sql=N'Select @start=UserID From ' + @TableName + 'Order By UserID'
Execute sp_execute @sql,N'@start int',@start
--Select @start=UserID From SZY_User Order By UserID --开始之前的一条记录,Where @WhereStr Set @i=@PageSize*@PageNum
Set RowCount @i
Select @end=UserID From SZY_User Order By UserID --结束记录 Set RowCount 0
Execute('Select ' + @ShowFields + ' From SZY_User Where UserID>' + @start + 'And UserID<=' + @end + ' Order By ' + @OrderFields)
End
End
GO
--如何将exec执行结果放入变量中? declare @num int, @sql nvarchar(4000)
set @sql='select @a=count(*) from tableName '
exec sp_executesql @sql,N'@a int output',@num output
select @num
Set @sql=N'Select @Sum=Count(*) From [' + @TableName + '] Where ' + @WhereStr
Execute sp_executesql @sql,N'@Sum int',@Sum --获得总记录数这条语句是对的,可是,到了这句就出错呢Set @sql=N'Select @start=UserID From ' + @TableName + 'Order By UserID'
Execute sp_execute @sql,N'@start int',@start
我不需要output---------------你這個需要output,不然@start沒辦法得到值