近日做项目中需要在EXEC执行Sql字符串时动态的传入参数并接收返回值,于是研究了一下SqlServer中sp_executesql的使用方法,并做了如下的例子。
在使用sp_executesql动态传入与接收返回参数时需注意以下事项,以避免大家走弯路。
例子中@SQLString,@ParmDefinition一定要使用NVARCHAR类型,否则会报“过程需要参数 '××××' 为 'ntext/nchar/nvarchar' 类型。”错误。
文档中说这两个变量赋值的字符串前要加N,我试了试,没加也不报错,不过你最好加上。毕竟人家是官方文档么。
--
传递一个Int类型参数
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR ( 500 )
DECLARE @ParmDefinition NVARCHAR ( 500 )
SET @SQLString = N ' SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level '
SET @ParmDefinition = N ' @level tinyint '
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString , @ParmDefinition , @level = @IntVariable
-- SET @IntVariable = 32
-- EXECUTE sp_executesql @SQLString, @ParmDefinition,@level = @IntVariable
-- -----------------------------------------------------------------------------
-- 传递一个varchar类型参数
DECLARE @VarVariable varchar ( 500 )
DECLARE @SQLString NVARCHAR ( 500 )
DECLARE @ParmDefinition NVARCHAR ( 500 )
SET @SQLString = N ' SELECT * FROM pubs.dbo.employee WHERE fname = @VarVariable '
SET @ParmDefinition = N ' @VarVariable varchar(500) '
SET @VarVariable = ' Helen '
EXECUTE sp_executesql @SQLString , @ParmDefinition , @VarVariable
-- -----------------------------------------------------------------------------
-- 传递两个参数,一个varchar类型参数接收返回值,一个Int类型参数
DECLARE @VarVariable VARCHAR ( 500 )
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR ( 500 )
DECLARE @ParmDefinition NVARCHAR ( 500 )
SET @SQLString = N ' SELECT @VarVariable = Count(*) FROM pubs.dbo.employee WHERE job_lvl = @IntVariable '
SET @IntVariable = 35
SET @ParmDefinition = N ' @VarVariable VARCHAR(500) OUT,@IntVariable INT '
SET @VarVariable = ' Helen '
EXECUTE sp_executesql @SQLString , @ParmDefinition , @VarVariable OUT, @IntVariable
print @VarVariable
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR ( 500 )
DECLARE @ParmDefinition NVARCHAR ( 500 )
SET @SQLString = N ' SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level '
SET @ParmDefinition = N ' @level tinyint '
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString , @ParmDefinition , @level = @IntVariable
-- SET @IntVariable = 32
-- EXECUTE sp_executesql @SQLString, @ParmDefinition,@level = @IntVariable
-- -----------------------------------------------------------------------------
-- 传递一个varchar类型参数
DECLARE @VarVariable varchar ( 500 )
DECLARE @SQLString NVARCHAR ( 500 )
DECLARE @ParmDefinition NVARCHAR ( 500 )
SET @SQLString = N ' SELECT * FROM pubs.dbo.employee WHERE fname = @VarVariable '
SET @ParmDefinition = N ' @VarVariable varchar(500) '
SET @VarVariable = ' Helen '
EXECUTE sp_executesql @SQLString , @ParmDefinition , @VarVariable
-- -----------------------------------------------------------------------------
-- 传递两个参数,一个varchar类型参数接收返回值,一个Int类型参数
DECLARE @VarVariable VARCHAR ( 500 )
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR ( 500 )
DECLARE @ParmDefinition NVARCHAR ( 500 )
SET @SQLString = N ' SELECT @VarVariable = Count(*) FROM pubs.dbo.employee WHERE job_lvl = @IntVariable '
SET @IntVariable = 35
SET @ParmDefinition = N ' @VarVariable VARCHAR(500) OUT,@IntVariable INT '
SET @VarVariable = ' Helen '
EXECUTE sp_executesql @SQLString , @ParmDefinition , @VarVariable OUT, @IntVariable
print @VarVariable