SQL-Server Objekte > Benutzerdefinierte Funktionen

Benutzerdefinierte Funktionen werden mit der CREATE FUNCTION-Anweisung erstellt und jeder benutzerdefinierte Funktionsname muss eindeutig sein. Des Weiteren gelten die Regeln des MS SQL-Servers zur Definition, Änderung und Nutzung von benutzerdefinierten Funktionen.

Eine benutzerdefinierte Funktion verwendet null oder mehr Eingabeparameter und gibt einen Wert oder eine Tabelle zurück. Eine Funktion kann maximal 1024 Eingabeparameter haben.

Für die Namen der Funktionen müssen folgende Namenskonventionen eingehalten werden:

a)Präfix "fnc_user"

b)Die nachfolgende Namenseingabe darf nicht nur aus Nummern bestehen z.B. nicht fnc_user_2, fnc_user_56, …

c)Zulässige Zeichen sind : a-z, A-Z, 0-9, _

d)Der gesamte Name inkl. Präfix muss mindestens 9 Zeichen aber höchstens 63 Zeichen lang sein.

Hinweis: rimacon omniSuite kann nur die selbst erstellten benutzerdefinierten Funktionen verwalten. Falls solch eine benutzerdefinierte Funktionen woanders (z.B. über SQL Server Management Studio) geändert wird, wird zwar die neue Definition gelten, aber beim Öffnen in der Management Console erscheint die alte Definition und nach dem Speichern wird die von SQL Server Management Studio erstellte Funktion überschrieben.

Benutzerdefinierte Funktionen, die außerhalb von rimacon omniSuite erstellt wurden, sind für rimacon omniSuite nicht sichtbar.

Für benutzerdefinierte Funktionen bestehen Einschränkungen für Parametertypen und für Ergebnistypen. Ein Ergebnistyp kann auch eine Tabelle sein. Funktionen mit Tabellenrückgabe können ähnlich wie Tabellen genutzt werden -> „select * from dbo.fnc_user_xxx()"

Beispiel:

create function dbo.fnc_user_RechnernameToTyp(@name varchar(50))

returns int as begin

declare @ret int

set @ret=1

if @name like 's%' and len(@name)=5 set @ret=2

if @name like 'nb%' set @ret=3

return @ret

end

Benutzerdefinierte Funktionen können genutzt werden

1)bei der Ermittlung der Ausgabewerte von dynamischen Spalten

2)in SQL-Sichten

3)bei dynamischen n:m-Beziehungen sowie

4)in allen SQL-Scripten

Benutzerdefinierte Funktionen erstellen/bearbeiten

Registerkarte „Allgemein“

Benutzen Sie diese Registerkarte, um die benutzerdefinierten Funktionen zu erstellen und zu bearbeiten.

Name

Gibt den Namen der benutzerdefinierten Funktion an.

Parameter

▪Zeigt den Rückgabewert und den Typ (Zeichenfolge, numerisch, Tabellenname, etc.) an.

▪Zeigt die Parameter und die Typen der Parameter entsprechend der vorgegebenen Reihenfolge an.

nach unten bzw. nach oben

Mit diesen Optionen kann die Reihenfolge der Parameter geändert werden.

hinzufügen und bearbeiten

Rückgabewert oder Parameter hinzufügen oder ändern. Wird eine Tabelle zurückgegeben, müssen die Tabellenparameter eingegeben werden.

löschen

Angegebenen Rückgabewert oder Parameter löschen.

Assistent

Über den Assistenten werden SQL-Anweisungen generiert, um z.B. bei rekursiven Beziehungen Werte von niedrigeren Stufen zur nächsthöheren Stufe zu aggregieren.

Beispiel:

Auf jeder Standortebene soll ermittelt werden, wie viele Rechner sich an jeder Ebene befinden bzw. zu jeder Ebene gehören -> … Anzahl Rechner in Hollenstedt -> Anzahl Rechner in Niedersachsen -> Anzahl Rechner in Deutschland.

Nach Auswahl der entsprechenden Tabelle und der „Parent-Spalte“ der Spalte werden z.B. für den Standort folgende SQL-Anweisungen generiert:

declare @mdtid int

declare @matrix table(parentid int,childid int)

declare @new table(parentid int,childid int)

set @mdtid=24

insert @matrix select STO_ID, STO_ID from STO where STO_MDT_ID=@mdtid

while 1 = 1

begin

delete from @new

insert @new select ret.parentid, STO_ID from @matrix ret

inner join STO on STO_MDT_ID=@mdtid and STO_STO_ID=ret.childid and STO_STO_ID<>0

delete @new from @new new

inner join @matrix ret on ret.parentid=new.parentid and ret.childid=new.childid

if not exists (select parentid from @new) break -- nichts neues ==> end of loop

insert @matrix select parentid,childid from @new -- neue append to @matrix

end

insert @return select parentid, childid from @matrix

return

SQL-Anweisungen müssen nun vor der return-Anweisung definiert werden, um die Anzahl der Rechner auf jeder Ebene zu ermitteln:

insert @return

select distinct parentid, HWM_ID from @matrix

inner join HWM on HWM.VCOL_Standort=childid

SQL

Definition des SQL-Scripts für die benutzerdefinierte Funktion, wobei die Anweisungen für das Anlegen der benutzerdefinierten Funktion „Create function dbo.() und returns int as begin .. end“ generiert werden.

Registerkarte „Beschreibung“

Beschreibung

Dokumentation der benutzerdefinierten Funktion

Registerkarte „SQL-Script“

Zeigt alle von Ihnen erstellten und alle generierten SQL-Anweisungen für die benutzerdefinierte Funktion an.

Registerkarte „Test“

Beschreibung

Testet die Korrektheit der benutzerdefinierten Funktion und zeigt das entsprechende Ergebnis an.