declare @aa varchar(50) set @aa='select * from table' exec(@aa)
declare @aa varchar(50) set @aa='select * from table' EXEC(@AA)
declare @aa varchar(50) set @aa='select * from table' exec(@aa)--使用动态SQL
动态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 = '[name]' Select @fname from sysobjects -- 错误 Exec('select ' + @fname + ' from sysobjects') -- 请注意 加号前后的 单引号的边上要加空格 exec sp_executesql N' select ' + @fname + ' from sysobjects' 当然将字符串改成变量的形式也可 declare @s varchar(1000) set @s = 'select ' + @fname + ' from sysobjects' Exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from sysobjects' Exec(@s) -- 成功 exec sp_executesql @s -- 此句正确,3: 输出参数 eg: declare @num, @sqls set @sqls='select count(*) from ' + @servername + '.a.dbo.b' exec(@sqls) 我如何能将exec执行的结果存入变量@num中declare @num int, @sqls nvarchar(4000) set @sqls='select @a=count(*) from '+@servername+'.a.dbo.b' exec sp_executesql @sqls,N'@a int output',@num output select @num
分页存储过程: ALTER proc Page_Count (@Table varchar(50),--表名 @Filter varchar(1000),--返回字段:用','隔开 @Count int, --每页显示记录数 @id varchar(50),--用来排序的字段 @CurrPage int --当前页数 ) as declare @Sql varchar(8000) if(@CurrPage=1) begin set @Sql='select top '+ cast(@Count as varchar) + ' '+ @Filter + ' from ' + @Table + ' order by ' + @id end else begin set @Sql='select top '+ cast(@Count as varchar) + ' '+ @Filter + ' from ' + @Table + ' where ' + @id + ' not in (select top '+ cast((@CurrPage-1)*@Count as varchar) + ' ' + @id + ' from ' + @Table + ' order by ' + @id + ')' end EXEc(@Sql)
set @aa='select * from table'
exec(@aa)
set @aa='select * from table'
EXEC(@AA)
declare @aa varchar(50)
set @aa='select * from table'
exec(@aa)--使用动态SQL
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 = '[name]'
Select @fname from sysobjects -- 错误
Exec('select ' + @fname + ' from sysobjects') -- 请注意 加号前后的 单引号的边上要加空格
exec sp_executesql N' select ' + @fname + ' from sysobjects'
当然将字符串改成变量的形式也可
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确,3: 输出参数
eg:
declare @num,
@sqls
set @sqls='select count(*) from ' + @servername + '.a.dbo.b'
exec(@sqls)
我如何能将exec执行的结果存入变量@num中declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from '+@servername+'.a.dbo.b'
exec sp_executesql @sqls,N'@a int output',@num output
select @num
分页存储过程:
ALTER proc Page_Count
(@Table varchar(50),--表名
@Filter varchar(1000),--返回字段:用','隔开
@Count int, --每页显示记录数
@id varchar(50),--用来排序的字段
@CurrPage int --当前页数
)
as
declare @Sql varchar(8000)
if(@CurrPage=1)
begin
set @Sql='select top '+ cast(@Count as varchar) + ' '+ @Filter + ' from ' + @Table + ' order by ' + @id
end
else
begin
set @Sql='select top '+ cast(@Count as varchar) + ' '+ @Filter + ' from ' + @Table + ' where ' + @id +
' not in (select top '+ cast((@CurrPage-1)*@Count as varchar) + ' ' + @id + ' from ' + @Table + ' order by ' + @id + ')'
end
EXEc(@Sql)