User defined types in SQL Server

Zuletzt hatte ich das Problem, dass ich temporäre Daten in einer TABLE-Variable hatte und diese in einer Tabellenwert Funktion weiter verarbeiten musste. Problem dabei war, wie gebe ich meine Tabelle an die Funktion weiter?

Die erste Idee war, dass ich eine temporäre Tabelle erstelle und die Daten dort platziere und dann in der Funktion lese – tsja, dass geht leider nicht, bzw. dies lässt der SQL Server nicht zu.

Okay, und nun? Da diese Funktionalität ständig zur Verfügung (zur Verwendung in einem SSRS Bericht) stehen musste, brauchte ich eine stabile und multiuser-fähige Lösung.

Nächste Idee wäre, eine physische Tabelle zu erstellen und darin die Daten zu speichern. Für die multiuser-Fähigkeit könnte ich Prozess- oder die Benutzer ID aufnehmen… ist aber irgendwie umständlich, da einerseits Berechtigungen auf die Tabelle erteilt werden müssen, Indizierung, Speicher etc …

Die Lösung war nun, benutzerdefinierte Typen im SQL Server zu erstellen. Bisher kannte ich das mehr von CLR’s, aber der SQL Server bietet bereits seit Version 2008 die Möglichkeit, eigene Typen für Tabellenwert-Variablen zu erstellen.

Via CREATE TYPE wird der Typ mit der entsprechenden Definition erstellt, bspw.:

CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO

Aus diesem Type kann dann ein entsprechendes Objekt erstellt werden:

DECLARE @tmpDataTable As tmpDataType;

Dieses Objekt kann dann wie eine bisherige Tabellenwert Variable mit Daten befüllt werden. Das Verhalten ist völlig identisch mit den bisherigen Tabellewert Variablen, die Daten können darin manipuliert, ergänzt oder gelöscht werden.  Der eigentliche Vorteil ist nun, dass ich dieses Objekt an eine Funktion weitergeben kann:

CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
DECLARE @tmpDataTable As tmpDataType;
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO

 

Das war’s schon!

 

 

Vollständiges Demo-Script (Verwendung auf eigene Gefahr!)

-- create a tmp.-DB
CREATE DATABASE TYPETEST;
GO
USE TYPETEST;
GO
-- create a demo table
CREATE TABLE dbo.data (
 id INT not null primary key identity(1,1)
 , data VARCHAR(255));
GO
-- insert demo data
INSERT INTO dbo.data (data) VALUES ('VAL1'), ('VAL2'), ('VAL3');
GO
-- create a temp table
CREATE TABLE ##tmpData (data VARCHAR(255));
GO
-- fill the demo table
INSERT INTO ##tmpData
SELECT data FROM dbo.data;
GO
-- create a function to read the demo data from the temp table
--CREATE FUNCTION dbo.testFunc()
--RETURNS TABLE
--RETURN (
-- SELECT data FROM ##tmpData
--);
--GO
-- Does not work, in functions you do not have the option to read from temporaly tables ...
-- and now?!
-- but how can i move my temporally data to my function?
-- one option is to create a pyhsically table with the process id ... but do you need this for sure?!
-- NO!
-- create a user type table variable in the database (be carefull, this is in the database a global type!!
CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO
-- create a function with a parameter from the user defined typ
CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
-- create a object from our new type
DECLARE @tmpDataTable As tmpDataType;
-- fill our object with demo data
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
-- execute our new function
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO
-- clean up
DROP TABLE ##tmpData;
GO
USE tempdb;
GO
DROP DATABASE TYPETEST;

Schreibe einen Kommentar