Using custom types as arrays in T-SQL
One of the missing language features in T-SQL language is array support. In some cases you can use custom types to work with arrays that are passed as parameters to your stored procedures.
Custom types in T-SQL enable you to create alias for some table, .Net or built-in type. Once you create a custom type, you can use it for local variables and parameters of functions.
The following statements create int and string arrays as a tables with one column with specified type:
create type "int" as table (value int); create type "string" as table (value nvarchar(4000));
You can insert or delete rows from this array using standard T-SQL DML operations.
Objects of this type cannot be stored in other tables, but you can use them as parameters of T-SQL functions:
create function dbo.sum(@array "int" readonly) returns int as begin return (select sum(value) from @array) end; go create function dbo.avgLength(@array "string" readonly) returns int as begin return (select avg(len(value)) from @array) end;
Now you can create a local array variable, fill it with some values and pass it to the function as input parameters:
declare @t as "int"; insert into @t values(1),(2),(3); select dbo.sum(@t); go declare @t as "string"; insert into @t values('test'),('sql'),('server'); select dbo.avgLength(@t);