As of SQL Server 2008, you can create user-defined datatypes as tables, which comes in pretty handy when you want to pass an entire table as an argument to a stored procedure or a function. And as a bonus, you get get really pretty, highly readable code!
As you know, you can define temp tables in variables. And just like with other datatypes, you can use the table datatype as a template to define your own table type, a user-defined datatype that is actually a table. Here’s how:
CREATE TYPE dbo.wordList AS TABLE ( word varchar(50) NOT NULL, PRIMARY KEY CLUSTERED (word));
Note that table types, like all user-defined types come with a schema assignment.
From here on, you can use dbo.wordList as if it were any user-defined datatype.
A table type variable is equivalent to a table variable. Remember that this means it isn’t optimized for large loads, and you should only use table variables with very little data in them (a commonly agreed guideline is 100 rows or less).
Example: A function that joins a table of values
Here’s a function that merges a list (a table) of words into a single string. Note that in order to be able to use a table type as an argument to the function, we have to include the READONLY keyword, so the function code cannot make changes to the contents of the @wordList variable.
CREATE FUNCTION dbo.fnJoinWordList( @wordList dbo.wordList READONLY, @separator varchar(10)=', ') RETURNS varchar(max) AS BEGIN; --- The output string that will contain the joined values: DECLARE @joined varchar(max); --- A recursive common table expression that will pick one --- value at a time from the @wordList table and join them --- together in @joined: WITH rcte (n, joined) AS ( --- Anchor: n=0, joined=NULL SELECT 0 AS n, CAST(NULL AS varchar(max)) AS joined UNION ALL --- The recursion: SELECT rcte.n+1 AS n, CAST(ISNULL([email protected], '')+ work.word AS varchar(max)) AS joined FROM rcte CROSS APPLY ( --- With ROW_NUMBER(), we're assigning a --- unique number for each row in @wordList SELECT ROW_NUMBER() OVER (ORDER BY word) AS n, word FROM @wordList ) AS work WHERE rcte.n+1=work.n) --- The output: SELECT TOP 1 @joined=joined FROM rcte ORDER BY n DESC RETURN @joined; END;
And here’s how to call the function using a table type variable:
DECLARE @list dbo.wordList; INSERT INTO @list VALUES ('a'), ('b'), ('c'); SELECT dbo.fnJoinWordList(@list, ', ');