Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '+ '.
When you try to execute the following code, you will receive the above error message.
Copy codeThe Code is as follows:
DECLARE @ MyName NVARCHAR (100)
DECLARE @ FieldName SYSNAME = n' name'
EXECUTE sp_executesql n' select top 1 @ OutputName = ['+ @ FieldName +'] FROM [dbo]. [Member] ',
N'@ OutputName NVARCHAR (100) output ',
@ MyName OUTPUT;
SELECT @ MyName
The problem is that more complex Unicode expressions are not allowed (for example, using the + operator to connect two strings ). See http://technet.microsoft.com/zh-cn/library/ms188001.aspx
[@ Statement =] statement
Unicode string that contains a Transact-SQL statement or batch processing. Statement must be a Unicode constant or a Unicode variable. More complex Unicode expressions are not allowed (for example, using the + operator to connect two strings ). Character constants are not allowed. If a Unicode constant is specified, N must be used as the prefix. For example, the Unicode constant n'sp _ who 'is valid, but the character constant 'SP _ who' is invalid. The size of the string is limited only by the memory of the available database server. In a 64-bit server, the string size is limited to 2 GB, that is, the maximum nvarchar (max) size.
To solve the problem, you can DECLARE a variable in the following code: DECLARE @ SQL NVARCHAR (MAX). assign a value to the variable for the SQL statement with the dynamic data name, table name, or field, use this variable to pass in sp_executesql.
Copy codeThe Code is as follows:
DECLARE @ MyName NVARCHAR (100)
DECLARE @ FieldName SYSNAME = n' name'
DECLARE @ SQL NVARCHAR (MAX) = n' SELECT TOP 1 @ OutputName = ['+ @ FieldName +'] FROM [dbo]. [Member]'
EXECUTE sp_executesql @ SQL,
N'@ OutputName NVARCHAR (100) output ',
@ MyName OUTPUT;
SELECT @ MyName
The problem can be easily solved.