How can I define a table type in SQL server when one of the columns is an array of decimals? I'm trying to pass to stored procedure an .NET object one of the fields of which is an array of decimals.
Thanks
6
-
4The normal way is to simply use a separate table. If you really want, you can use XML.Gordon Linoff– Gordon Linoff2015-05-10 14:00:10 +00:00Commented May 10, 2015 at 14:00
-
or the worst possible solution: comma-delimited values.Zohar Peled– Zohar Peled2015-05-10 14:01:17 +00:00Commented May 10, 2015 at 14:01
-
@Gordon Linoff I've added some explanation to my question. Thank youIlan– Ilan2015-05-10 14:08:00 +00:00Commented May 10, 2015 at 14:08
-
2... Or serialize it and store as xml :-)Zohar Peled– Zohar Peled2015-05-10 14:10:14 +00:00Commented May 10, 2015 at 14:10
-
1Check out Erland Sommarskog's page on Arrays in SQL Server. I feel like I post this link all the time, but it is really useful. He provides some great examples in his section on XML too.Mr Moose– Mr Moose2015-05-10 14:12:34 +00:00Commented May 10, 2015 at 14:12
|
Show 1 more comment
1 Answer
t-sql does not support arrays.
You do, however, have some options: here are 3 of them, from the best to the worst:
- Create 2 table types, have a column in one act as a foreign key to the other.
- Create a table type with a varchar(max) column that will hold your decimal values as a comma delimited string.
- Create a table type with an
xmldata type column.
2 Comments
MatteoSp
I generally agree, but second is worst (from data handling, performance as size points of view) than third.
Zohar Peled
@MatteoSp in this case, you are correct, and I've edited my answer according to your comment. in other cases xml data type might be a more appropriate solution, even when considering it's size overhead.