Using custom types as arrays in T-SQL

Published Mar 23 2019 05:46 PM 2,328 Views
Microsoft
First published on MSDN on Jul 06, 2018
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);





Version history
Last update:
‎Mar 23 2019 05:46 PM
Updated by: