Question

sql查询字符串拼接

如图中的拼接方法 怎么拼接才正确呢？

Answer 1

拼串的时候用char(39) 代替单引号,char(37)代替%

SET @sql = 'SELECT * FROM authors WHERE address LIKE '+char(39)+char(37)+@str+char(37)+char(39)

Answer 2

不要用双引号 只用单引号

SET @sql = 'SELECT * FROM authors WHERE address LIKE ''%'+@str+'%'''

Answer 3

定义\为转义字符
SET @sql = 'SELECT * FROM authors WHERE address LIKE '\'%\'+@str+\'%\'' ESCAPE '\'

Answer 4

带参数的不能这么调用。参考MSDN的sp_executesql
sp_executesql [ @stmt = ] stmt

[
{, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]

例：
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;