动态SQL语句[MSSQL防范]

1:普通SQL语句可以用Exec履行
eg:?? Select * from tableName
????? Exec('select * from tableName')
????? 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 　　以上是“动态SQL语句[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：