Problem mit Parameter bei einer gespeicherten Prozedur

Question

Hallo,

ich habe eine Frage zu Parametern in gespeicherten Prozeduren. Ich habe eine Variable definiert:

ALTER PROCEDURE LoadSchedulerTreeViewFromUserGroup

	(
	@GroupName nvarchar(255)
	/*@parameter2 datatype OUTPUT*/
	)

AS

Die Abfrage dazu:

WITH cte_player AS 
(
	SELECT        ...
     FROM            cms_player INNER JOIN cms_group_player ON cms_player.playerID = cms_group_player.playerID
     WHERE        (cms_group_player.GroupName IN (@GroupName))
     UNION ALL
     SELECT        ...
     FROM            cms_player AS a INNER JOIN cte_player AS v ON v.parentID = a.playerID
)
SELECT DISTINCT ...

return

Wenn ich der Variable einen Name zuweise, funktioniert die Abfrage. Sobald ich aber mehrere Werte mit Komma getrennt übergebe, erhalte ich als Anzahl 0.

USE [uebung1DB]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[LoadSchedulerTreeViewFromUserGroup]
		@GroupName = N'administrators,cmsuser'

SELECT	'Return Value' = @return_value

GO

Wie kann ich mein Problem lösen?

Reiner

Answer 1

Hallo Reiner,

@GroupName ist ein skalarer String und somit einspricht "GroupName IN (@GroupName)" eigentlich einer "GroupName = @GroupName"; oder anders gesagt, der String wird nicht als komma-separierte Liste gewertet und somit geht das IN nicht.

Du könntest per Like prüfen, ob der Gruppenname im String enthalten ist:

WITH cte_player AS 
(
	SELECT        ...
     FROM            cms_player INNER JOIN cms_group_player ON cms_player.playerID = cms_group_player.playerID
     WHERE        (@GroupName LIKE '%' + cms_group_player.GroupName + '%,')
     UNION ALL
     SELECT        ...
     FROM            cms_player AS a INNER JOIN cte_player AS v ON v.parentID = a.playerID
)
SELECT DISTINCT ...

return

Zuvor solltest Du ein abschließendes Komma anhängen, damit es klappt. Aber: Durch die Funktion aufs Feld kann kein Index genutzt werden.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Die Variable wird als einzlener String betrachtet, d.h. aus @GroupName = N'administrators,cmsuser' wird in T-SQL .GroupName IN (N'administrators,cmsuser')).

Du hast drei Möglichkeiten:

1. Übergib eine Tabellenwertparameter
2. Zerlegen den Parameter innerhalb der Prozedur in eine Tabellenvariable (siehe Erlands Array-Artikel)
3. Benutze dynamisches SQL

Answer 3

Hallo Reiner,
du müsstest entweder das komplette Statement dynamisch zusammenbauen und dann ausführen, oder besser Tabellenwertparameter verwenden. Der Query Processor betrachtet Deinen Parameter als einen vollständigen Wert und nicht als Liste, wie von Dir gewünscht.

Dynamisches SQL : Fluch und Segen
http://www.insidesql.org/blogs/frankkalis/2004/07/16/dynamisches-sql-fluch-und-segen

Verwenden von Tabellenwertparameter (Datenbankmodul)
http://msdn.microsoft.com/de-de/library/bb510489(v=sql.105).aspx
 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 4

Danke für die schnelle Antwort, Olaf. So funktioniert es.

Gruß

Reiner