Ausgabe mit SQL server-Escape-character für SSRS-Parameter

In SSRS habe ich einen Parameter von Office Manager Namen, die von einer gespeicherten Prozedur gefüllt wird:

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 

Der Parameter ist wie folgt gefüllt:

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

In SQL server habe ich ein weiteres SP, das den Office Manager Namen aus einer Tabelle mit einem functionsplitter verbindet:

  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, ',')) 

Die function Splitter-Code sieht so aus:

  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 

Das Problem, das ich habe, ist, dass alle datasätze mit Office Manager Tom O'Toole nicht in den Bericht erscheinen, wenn ich alle Manager aus dem Parameter auswählen. Er zeigt sich, wenn ich O'Toole Tom aus der Parameterliste auswähle. Ich denke, es hat mit dem Apostroph in seinem Nachnamen zu tun, da er der einzige Manager mit einem Apostroph ist. Wenn dies der Fall ist, weiß jemand, wie man ein Escape-character in diesem Code benutzt, um O'Toole zu bevölkern zu bekommen?

Ich habe das Problem mit SQL server Profiler gefunden. Was das Reporting an den server sendet, ist:

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

Wie Sie anstelle von 2 Apostrophen sehen können, sendet es 4: O''''Toole Tom .

Bei der Aufteilung dieses Stachels mit DBO.FNSPLIT hast du O''Toole Tom statt O'Toole Tom und das findet keine Spiele in deiner Tabelle.

Das Workaroud ist das folgende: replace Sie 4 Apostrophe mit 2 in Ihrem gespeicherten Prozedur wie folgt, Hinzufügen zusätzlicher Variable @ 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) ist Apostroph, ich benutze seinen Code anstelle des Symbols für nicht verrückt mit '' '' '

PS Sie verwenden SSRS 2016, nicht 2012

UPDATE1

Ich habe gerade versucht, aber es funktioniert immer noch nicht. Es funktioniert auch nicht, wenn ich nur Tom O'Toole anstelle von All wähle, was zuvor gearbeitet hat.

Ich habe diesen Code noch einmal für beide Fälle getestet, hier ist mein Repro:

 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)), ','); 

Ich habe 2 Streicher für beide Fälle eingefügt und angewendet DBO.FNSPLIT zu diesen Strings direkt und nach dem replace , der zweite path arbeitete in beiden Fällen:

Bildbeschreibung hier eingeben

UPDATE2

So finden Sie den ersten Parameter, der an die gespeicherte Prozedur übergeben wurde.

Zuerst ALTER Ihr Verfahren, das wird den alten Plan ungültig machen. Dann lade es von SSRS mit dem problematischen Parameter. Dieser Parameter wird schnüffelt, da es die erste Ausführung ist. Dann können Sie es als ParameterCompiledValue grafisch oder in xml Ihres Prozedurplans sehen.

Hier ist der Code, um den Proc-Plan aus dem Cache zu holen:

 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 

Bildbeschreibung hier eingeben

Das Problem ist "Symbol" im Namen von Ton, versuchen Sie, den Code hinzuzufügen, der das Ersetzen ersetzt

  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,'''',''''''), ','))