sql sp_executesql 动态列或动态表名参数
在使用sp_executesql执行动态的sql时,如果传递进来的参数是表名或者是列名,那么会执行不成功。
代码如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 alter proc test
2
3 as
4 declare @value nvarchar(400)
5 declare @sql nvarchar(400)
6 declare @tempvalue datetime
7 declare @id bigint
8 set @id = 226
9 set @tempvalue = getdate()
10 set @value='exportTime'
11 set @sql=N'update counter set @value1=@tempvalue1 where id= @id1'
12
13 exec sp_executesql @sql,
14 N' @tempvalue1 as datetime,@id1 as bigint',
15 @value1 = @value,
16 @tempvalue1= @tempvalue,
17 @id1= @id;
2
3 as
4 declare @value nvarchar(400)
5 declare @sql nvarchar(400)
6 declare @tempvalue datetime
7 declare @id bigint
8 set @id = 226
9 set @tempvalue = getdate()
10 set @value='exportTime'
11 set @sql=N'update counter set @value1=@tempvalue1 where id= @id1'
12
13 exec sp_executesql @sql,
14 N' @tempvalue1 as datetime,@id1 as bigint',
15 @value1 = @value,
16 @tempvalue1= @tempvalue,
17 @id1= @id;
以上代码并不能更改表的内容。
解决方法:应该使用quotename(表名/列名) 来表示变量。
以上代码修改如下:
alter proc test
as
declare @value nvarchar(400)
declare @sql nvarchar(400)
declare @tempvalue datetime
declare @id bigint
set @id = 226
set @tempvalue = getdate()
set @value='exportTime'
set @sql = N'update counter set '+quotename(@value) +'='+ '@tempvalue1 where id =@id1 '
exec sp_executesql @sql,
N' @tempvalue1 as datetime,@id1 as bigint',
@tempvalue1= @tempvalue,
@id1= @id;
as
declare @value nvarchar(400)
declare @sql nvarchar(400)
declare @tempvalue datetime
declare @id bigint
set @id = 226
set @tempvalue = getdate()
set @value='exportTime'
set @sql = N'update counter set '+quotename(@value) +'='+ '@tempvalue1 where id =@id1 '
exec sp_executesql @sql,
N' @tempvalue1 as datetime,@id1 as bigint',
@tempvalue1= @tempvalue,
@id1= @id;
以上代码可以正常的执行