一、基本注意点
sp_executesql 的参数必须为UNICODE,即NCHAR,NVARCHAR,NTEXT型,否则报错
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
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
如果是自己的存储过程中有参数,也可以不用sp_executesql,直接
CREATE PROCEDURE demo
@test varchar(100)
as
return 1
go
declare @name int
exec @name=demo 'test'
select @name
存储过程demo的参数中不能有函数
,如果要传递一个newid(),必须使用局部变量
注意存储过程的返回值必须为整形
declare @id
declare @name int
exec @name=demo @id
select @name
mssql 帮助
执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。
语法
sp_executesql [@stmt =] stmt
[
{, [@params =] N@#@parameter_name data_type [,...n]@# }
{, [@param1 =] @#value1@# [,...n] }
]
参数
[@stmt =] stmt
包含 Transact-SQL 语句或批处理的 Unicode 字符串，stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符串联两个字符串）。不允许使用字符常量。如果指定常量，则必须使用 N 作为前缀。例如，Unicode 常量 N@#sp_who@# 是有效的，但是字符常量 @#sp_who@# 则无效。字符串的大小仅受可用数据库服务器内存限制。
stmt 可以包含与变量名形式相同的参数，例如：
N@#SELECT * FROM Employees WHERE EmployeeID = @IDParameter@#
stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。
[@params =] N@#@parameter_name data_type [,...n]@#
字符串，其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。
[@param1 =] @#value1@#
参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数，则不需要值。
n
附加参数的值的占位符。这些值只能是常量或变量，而不能是更复杂的表达式，例如函数或使用运算符生成的表达式。
返回代码值
0（成功）或 1（失败）
结果集
从生成 SQL 字符串的所有 SQL 语句返回结果集。
例子（感谢邹建提供）
declare @user varchar(1000) declare @moTable varchar(20) select @moTable = @#MT_10@# set @sql=@#select @user = count(distinct userid) from @#+@moTable --为变量赋值 --执行@sql中的语句 print @user |
二、列名无效
因为在拼凑SQL时，没有把相应的单引号加上，char(39)可表示单引号。
例1：
DECLARE
@m_LastName
nvarchar
(
20)
DECLARE
@Sql
varchar
(
256)
SET
@m_LastName
=
'
Fuller
'
SET
@Sql
=
'
SELECT * FROM Employees WHERE LastName=
'
+
char
(
39
)
+
@m_LastName
+
char
(
39)
EXEC
(
例2：
declare @s nvarchar(1000)
declare @num int
set @s = 'select @count = count(*)
from wms_rest_statistic wrs
left join v_user_info vi
on vi.UserID = wrs.userID
where (wrs.userID = ' + cast(@pi_userid as nvarchar(10)) + ' or ' + cast(@pi_userid as nvarchar(10)) + ' = 0)' +
' and wrs.wms_year = ' + cast(@pi_year as nvarchar(10)) +
' and (wrs.cdeptcode like ''' + @pi_dept + '%'' or ''' + @pi_dept + ''' = '''')' +
' and (EXISTS(select 1
from fn_searchUser(' + cast(@Pi_curuser as nvarchar(10)) + ') fn' +
' where fn.ref_UserID = wrs.userid) OR wrs.userid = ' + cast(@Pi_curuser as nvarchar(10)) + ')'
exec sp_executesql @s,N'@count int output',@num output
