declare @RecordCount int
set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec(@sqlt)这一句提示@RecordCont没有定义,这个到是知道
但set @sqlt = 'declare @RecordCount intSELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec(@sqlt)
这样也不对啊
我该如何获得值呢
set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec(@sqlt)这一句提示@RecordCont没有定义,这个到是知道
但set @sqlt = 'declare @RecordCount intSELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec(@sqlt)
这样也不对啊
我该如何获得值呢
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
Create PROCEDURE sp_page
@CurrentPage int, @PageSize int,@Field_Info varchar(500),@Table_info varchar(20),@Field_id varchar(10),@intOrder int,@otherwhere varchar(50),@RecordCount int output,@PageCount int output
--@CurrentPage为显示那一页,@PageSize为每一页显示几行,@Field_info为要显示的字段可以为*,@Table_info为要查询的表或视图,@field_id为按这个字段排序,@intorder0为升序排1为降序排,@otherwhere为条件,@RecordCount为总行数,@PageCount为总页数
AS
DECLARE @MinPage int, @MaxPage int
declare @sql varchar(1000)
declare @sqlt varchar(100)
declare @order varchar(4)
IF @PageSize <= 0
begin
set @PageSize = 10
end
set @sqlt = 'declare @RecordCount int SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec(@sqlt)
set @pagecount = @RecordCount / @pagesize --获得总页数
IF @CurrentPage <= 0
begin
set @CurrentPage = 1
end
else if @CurrentPage > @pagecount
begin
set @currentpage = @pagecount --如果输入页数大于总页数则符最后一页
end
SET @MinPage = (@CurrentPage - 1) * @PageSize + 1
SET @MaxPage = @MinPage + @PageSize - 1
BEGIN
if @intorder = 0
set @order = 'asc'
else
set @order = 'desc'
if @otherwhere like ''
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage)
else
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ' and ' + @otherwhere
exec(@sql)
END
declare @rcon int
declare @pcon int
exec sp_page 3,400,'id,name,password','user_info','id',0,'',@rcon output,@pcon output
print @rcon
declare @sqlt nvarchar(4000),@Table_Info varchar(100),@Field_id varchar(100)set @Table_Info='表名'
set @Field_id='F1'declare @RecordCount int
set @sqlt = 'SELECT @a = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec sp_executesql @sqlt,N'@a int output',@RecordCount output
要用sp_executesql 才行,因为变量的生存期只在sql动态语句中,出了就无效了.用sp_executesql可以解决这个问题.因为可以传参数,可是exec()不能够传参数declare @RecordCount int
set @sqlt = 'SELECT @a = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec sp_executesql @sqlt,N'@a int output',@RecordCount output
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num