在动态SQL中处理单引号的正确方法?

Question

我在下面有一个动态 sql 查询来创建一个函数，我用 4x 单引号替换了单引号，但它不起作用。

EXEC(
'
CREATE  OR ALTER FUNCTION dbo.fnGetNumberFromPCN
(
@PCN VARCHAR(50)
)
RETURNS BIGINT
AS
BEGIN
--Init
DECLARE @Number BIGINT = NULL
--Get
SELECT @Number = CASE WHEN PATINDEX(''''%[^0-9]%'''',STUFF(V.YourString,1,PI.I-1,'''''''')) > 0 
THEN CONVERT(BIGINT, SUBSTRING(V.YourString,PI.I,PATINDEX''''%[^0-9]%'''',STUFF(V.YourString,1,PI.I-1,''''''''))-1))
ELSE 0
END
FROM (VALUES(@PCN),(@PCN))V(YourString)
CROSS APPLY (VALUES(PATINDEX''''%[1-9]'''', V.YourString)))PI(I)
--Finally
RETURN @Number
END
')
GO

Answer 1

只需检查生成的 SQL，您就会发现问题：

declare @sql varchar(max) = '
CREATE  OR ALTER FUNCTION dbo.fnGetNumberFromPCN
(
@PCN VARCHAR(50)
)
RETURNS BIGINT
AS
BEGIN
--Init
DECLARE @Number BIGINT = NULL
--Get
SELECT @Number = CASE WHEN PATINDEX(''''%[^0-9]%'''',STUFF(V.YourString,1,PI.I-1,'''''''')) > 0 
THEN CONVERT(BIGINT, SUBSTRING(V.YourString,PI.I,PATINDEX''''%[^0-9]%'''',STUFF(V.YourString,1,PI.I-1,''''''''))-1))
ELSE 0
END
FROM (VALUES(@PCN),(@PCN))V(YourString)
CROSS APPLY (VALUES(PATINDEX''''%[1-9]'''', V.YourString)))PI(I)
--Finally
RETURN @Number
END
'
print @sql
--exec (@sql)

输出

CREATE  OR ALTER FUNCTION dbo.fnGetNumberFromPCN
(
@PCN VARCHAR(50)
)
RETURNS BIGINT
AS
BEGIN
--Init
DECLARE @Number BIGINT = NULL
--Get
SELECT @Number = CASE WHEN PATINDEX(''%[^0-9]%'',STUFF(V.YourString,1,PI.I-1,'''')) > 0 
THEN CONVERT(BIGINT, SUBSTRING(V.YourString,PI.I,PATINDEX''%[^0-9]%'',STUFF(V.YourString,1,PI.I-1,''''))-1))
ELSE 0
END
FROM (VALUES(@PCN),(@PCN))V(YourString)
CROSS APPLY (VALUES(PATINDEX''%[1-9]'', V.YourString)))PI(I)
--Finally
RETURN @Number
END

失败

Msg 102, Level 15, State 1, Procedure fnGetNumberFromPCN, Line 15 [Batch Start Line 0]
Incorrect syntax near ''.