与SSRS参数SQL Server的转义字符问题

Question

在SSRS我有一个由存储过程填充办公室经理的名字的参数：

SELECT 
      MANAGER.office_manager_name 
     FROM (SELECT 
        ISNULL(REPLACE(PRACT_LOC.officemanagername, ',', ''), '*N/A') AS office_manager_name 
        FROM PRACTICELOCATIONS PRACT_LOC 
        UNION 
        SELECT 
        '*N/A') MANAGER 
     ORDER BY 
      MANAGER.office_manager_name 

参数填充像这样：

*N/A, Smith John, Sharp Alex, O'Toole Tom 

在SQL Server我有另一个SP，使用功能分离装置从表中加入了办公室经理的名字：

CREATE PROCEDURE IP 
    (@office_manager_name VARCHAR(4000)) 
    AS 

    Select * from Table A 
    where ISNULL(REPLACE(A.officemanagername, ',', ''), '*N/A') IN (SELECT item FROM DBO.FNSPLIT(@officemanagername, ',')) 

功能分路器的代码如下所示：

ALTER FUNCTION [dbo].[fnSplit] 
    (
@sInputList VARCHAR(MAX), -- List of delimited items 
@sDelimiter VARCHAR(MAX) = ',' -- delimiter that separates items 
    ) 

    RETURNS @List TABLE (item VARCHAR(MAX)) 

    BEGIN 

    DECLARE @sItem VARCHAR(MAX) 

    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 
BEGIN 
    SELECT 
     @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX  (@sDelimiter,@sInputList,0)-1))), 
     @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) 

    IF LEN(@sItem) > 0 
     INSERT INTO @List SELECT @sItem 
END 

IF LEN(@sInputList) > 0 
    INSERT INTO @List SELECT @sInputList -- Put the last item in 
    RETURN 

    END   

我遇到的问题是，与业务经理汤姆·奥图尔任何记录不会在报告中显示，如果我选择所有的参数管理器。如果我从参数列表中选择O'Toole Tom，他确实会显示出来。我认为这与他的姓氏中的撇号有关，因为他是唯一一个带有撇号的经理。如果是这种情况，是否有人知道如何在此代码中使用转义字符来获取O'Toole？

Answer 1

的问题是“'”，在吨的名称符号，尝试添加代码替换波纹管

REPLACE(@officemanagername,'''','''''') 

---- 
CREATE PROCEDURE ##IP 
    (@office_manager_name VARCHAR(4000)) 
    AS 

    Select * from Table A 
    where ISNULL(REPLACE(A.officemanagername, ',', ''), '*N/A') IN (SELECT item FROM DBO.FNSPLIT(REPLACE(@officemanagername,'''',''''''), ',')) 

Answer 2

我使用SQL Server Profiler中发现的问题。 报表事件发送到服务器是这样的：

exec rsp_m_initialprocessxxxx @practice_manager=N'*N/A,O''''Toole Tom,Sharp Alex,Smith John' 

正如你所看到的，而不是2个省略号它发送4：O''''Toole Tom。

当使用DBO.FNSPLIT分割此蜇时，您已获得O''Toole Tom而不是O'Toole Tom，并且在您的表中找不到匹配项。

的workaroud如下：代替4撇号与2在像这样的存储过程，增加额外的变量@ practice_manager1：

declare @practice_manager1 varchar(4000) = (select replace(@practice_manager, replicate(char(39), 2), replicate(char(39), 1))); 

Select * from dbo.A 
where ISNULL(REPLACE(A.office_manager_name, ',', ''), '*N/A') IN (SELECT item FROM DBO.FNSPLIT(@practice_manager1, ',')); 

CHAR（39）是撇号，我使用它的代码，而不是符号的用''''不会变得疯狂''''

PS您正在使用SSRS 2016年，未在2012年

UPDATE1

我只是尝试这样做，但它仍然无法正常工作。如果我只选择Tom O'Toole而不是All，那么它也不起作用。

我已经测试此代码一个更多的时间用于这两种情况下，这是我的摄制：

declare @t table (test_num int, name varchar(4000)); 
insert into @t values (1, 'O''Toole Tom'), 
(2, '*N/A,O''''Toole Tom,Sharp Alex,Smith John'); 

SELECT test_num, name, item 
FROM @t cross apply DBO.FNSPLIT(name, ','); 

SELECT test_num, name, item 
FROM @t cross apply DBO.FNSPLIT(replace(name, replicate(char(39), 2), replicate(char(39), 1)), ','); 

我插入2串这两种情况下，直接应用于DBO.FNSPLIT该字符串，使后一个replace，第二路工作在两种情况下：

UPDATE2

如何查找的第一个参数传递给存储过程。

首先ALTER你的程序，这将使旧计划失效。 然后从具有问题参数的SSRS运行它。该参数将被嗅探，因为它是第一次执行。然后，您可以将其视为ParameterCompiledValue图形或xml您的程序计划。

这里是从缓存中抢PROC的计划代码：

select qp.query_plan 
from sys.dm_exec_procedure_stats ps 
    cross apply sys.dm_exec_query_plan(ps.plan_handle) qp 
where object_id = object_id('dbo.sp_test'); -- put here your sp name