Stored Functions in Subselects

[rausch]

Hallo @community,

SQL Profis aufgepasst ! Ich habe hier ein spezielles Problem.
Habe unter MS SQL 2005 eine Funktion entwickelt, die ich zur Rechteprüfung nutze. Diese baue ich in Subselects ein mit der Übergabe aus dem übergeordneten Select.
Unter MS SQL 2000 bekomme ich da Probleme. Die Funktion selbst funktioniert prima. Im Subselect auch, wenn ich fixe Werte übergebe. Nehme ich als Übergabe allerdings eine Spalte aus dem übergeordneten Select, dann erhalte ich die Fehlermeldung: Server: Nachr.-Nr. 170, Schweregrad 15, Status 1, Zeile 7.

Hier meine Funktion:

CREATE FUNCTION sf_MatchRights
(
@uid int = 0,
@matchids varchar(4000) = '',
@delimiter char(1) = ','
)
RETURNS @results TABLE ( returncode int )
AS
BEGIN
INSERT INTO @results VALUES (2)
DECLARE @value int

DECLARE cur CURSOR LOCAL FOR
SELECT [value] FROM fn_Split (@matchids,@delimiter)

OPEN cur
FETCH FROM cur INTO @value

WHILE @@FETCH_STATUS = 0
BEGIN

IF ( @value = @uid ) UPDATE @results SET returncode = 1
IF ( @value < 0 )
BEGIN
IF (SELECT COUNT(*) AS Counter FROM krgroupusers WHERE luserid=@uid AND lgroupid=(@value*-1))>0 UPDATE @results SET returncode = 1
END
FETCH NEXT FROM cur INTO @value
END

CLOSE cur
DEALLOCATE cur
RETURN
END


Hier mein Select:

SELECT lid, dtcreation AS xTime, dtedit AS xActivity, xstitle AS xTitle, xtcomment AS xDesc, lcreatorid AS xUid, 10024 AS xAppid
FROM xinnovator
WHERE xinnovator.xlcontact=@contact
AND (lcreatorid=@uid
OR (xlcategory IN (SELECT lid FROM xinnovatorcats WHERE (SELECT returncode FROM sf_MatchRights (@uid, xinnovatorcats.xtqkmembers,','))=1))
)

Ersetze ich nun im Aufruf der sf_MatchRights die Spalte xinnovatorcats.xtqkmembers durch z.B. '1|2|3', dann funktioniert es wie gewünscht.

Da man schon unter MS SQL 2000 übergeordnete Spalten im Subselect verwenden kann stehe ich etwas auf dem Schlauch, was das nun sein könnte.

Für einen Tip bin ich sehr dankbar, da ich die ganze Sache hier unter MS SQL 2000 irgendwie zum Laufen bekommen muss.

PS: Achso, hier noch die Split() Funktion, da SQL das (zumindest weiss ich das nicht) kann und ich mir selbst dazu eine kleine Funktion geschrieben habe (sollte man aber nicht auf grosse Tabellen loslassen ).

CREATE FUNCTION fn_Split(@text nvarchar(4000), @delimiter char(1) = ',')

RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value nvarchar(4000)
)

AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END

[hwoess]

Ich kann es jetzt nicht ausprobieren, aber ich habe den verdacht, das Problem tritt dann auf, wenn xinnovatorcats.xtqkmembers NULL ist. Probiere als Parameter vielleicht mal stattdessen IsNull(xinnovatorcats.xtqkmembers,''). Als Gegenprobe denselben Aufruf im SQL2005 machen und statt xinnovatorcats.xtqkmembers direkt NULL mitgeben.

bye,
Helmut


PS:

PS: Achso, hier noch die Split() Funktion, da SQL das (zumindest weiss ich das nicht) kann und ich mir selbst dazu eine kleine Funktion geschrieben habe (sollte man aber nicht auf grosse Tabellen loslassen ).

Pfui, man schmückt sich nicht mit fremden Federn :
http://www.odetocode.com/Articles/365.aspx

[rausch]

Ich kann es jetzt nicht ausprobieren, aber ich habe den verdacht, das Problem tritt dann auf, wenn xinnovatorcats.xtqkmembers NULL ist. Probiere als Parameter vielleicht mal stattdessen IsNull(xinnovatorcats.xtqkmembers,''). Als Gegenprobe denselben Aufruf im SQL2005 machen und statt xinnovatorcats.xtqkmembers direkt NULL mitgeben.

bye,
Helmut


PS:

Pfui, man schmückt sich nicht mit fremden Federn :
http://www.odetocode.com/Articles/365.aspx

Hmm, dazu kann ich nur sagen, dass ich in den MSDN Foren nach ähnlichen Probleme gesucht habe, dann mit diversen Hinweisen die Funktion geschrieben habe. Ich habe weder Lust (aus solchen Kommentaren resultierend) noch die Notwendigkeit Code zu "entwenden" und als "eigen" zu publizieren...

Zu Deinem Hinweis:
Erst mal Danke, da ich für jeden Tip dankbar bin.
Leider trotzdem:
Server: Nachr.-Nr. 170, Schweregrad 15, Status 1, Zeile 5
Zeile 5: Falsche Syntax in der Nähe von 'IsNull'.
Bekomme ich unter 2000 als Fehler. Mir scheint, dass ich, da ich die Übergabe als varchar definiert habe, nichts anderes als einen String übergeben kann. Sehr strange...

[O. Helper]

Hallo Yves,

wieso liefert sf_MatchRights überhaupt ein TABLE als Ergebnis?
Die Tabelle enthält bei Dir immer nur einen Wert.
Versuch doch mal, es als Skalarfunktion auszulegen und nur ein INT zurückzuliefern.

BTW, ich muss bei UDF immer das Schema mit angeben (also dbo.sf_MatchRights); warum Du nicht? Liegt die in der master oder habe mal wieder ich nur das Phänomen?

Olaf

[rausch]

Hallo Yves,

wieso liefert sf_MatchRights überhaupt ein TABLE als Ergebnis?
Die Tabelle enthält bei Dir immer nur einen Wert.
Versuch doch mal, es als Skalarfunktion auszulegen und nur ein INT zurückzuliefern.

BTW, ich muss bei UDF immer das Schema mit angeben (also dbo.sf_MatchRights); warum Du nicht? Liegt die in der master oder habe mal wieder ich nur das Phänomen?

Olaf

Das Frage ich mich auch .
Habe es mit Hilfe aus dem MSDN Forum umgestellt in eine Scalarfunktion:
create function sf_MatchRights
(
@uid int = 0,
@matchids varchar(4000) = '',
@delimiter char(1) = ','
)

returns int
as
begin

declare @returnval int
set @returnval = 2

declare @values table ([value] nvarchar(4000))
insert into @values
select [value]
from fn_Split(@matchids, @delimiter)

if exists (select *
from @values
where [value] = @uid)
begin
set @returnval = 1
end
else if exists (select *
from @values
where [value] < 0)
begin
if exists (select *
from krgroupusers k
inner join @values v
on k.lgroupid = (v.[value]*-1)
where luserid = @uid)
begin
set @returnval = 1
end
end
return @returnval
end

Dann kann ich diese direkt verwenden und dann scheint es zu gehen.
dbo. Muss ich bei Scalarfunktionen angeben, bei Table Functions anscheinend nicht?!