Recently doing the project needs to be in the exec SQL string when the dynamic incoming parameters and receive the return value, so the study of SQL Server in the use of sp_executesql, and do the following example.
In the use of sp_executesql dynamic incoming and receive return parameters should pay attention to the following things to avoid people detours.
Example @sqlstring, @ParmDefinition be sure to use the nvarchar type, otherwise it will be reported that "the process requires parameters ' xxxx ' for the ' Ntext/nchar/nvarchar ' type." Error
The document said that the two variables assigned to the string before to add N, I tried to try, did not add or error, but you'd better add. After all, are they official documents?
--Passing an int type argument
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
-------------------------------------------------------------------------------
--Passing a varchar type parameter
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
-------------------------------------------------------------------------------
--Pass two parameters, a varchar type parameter to receive the return value, an int type argument
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 ($) out, @IntVariable INT '
SET @VarVariable = ' Helen '
EXECUTE sp_executesql @SQLString, @ParmDefinition, @VarVariable out, @IntVariable
Print @VarVariable
sp_executesql Receive returns multiple argument instances