CODEWITHSUNDEEP
sql-servert-sqlsqldatatypes
Oleg
Oleg

Reputation: 1458

Structure types in T-SQL

As I know SQL Server doesn't support structure types that contains another types, for example you can't make something like this 

create type User as(UserId int, Name varchar(10), Address varchar(255))

and then use it:

declare @user User
set @user.UserId = 10
...

Maybe someone knows any library/framework that helps to emulate this behavior, for example it can be some set of functions that use XML data-type to hold in it values and retrieve them.

The problem that I'm trying to solve is passing large number of parameters between stored procedures. I have procedure that accept nearly 30 parameters than make some logic and path this parameters to another procedure, then second procedure make some logic and path parameters to third procedure. Then requirements changed and new parameters should be add to all procedures. With structure data-types it transforms to just adding new fields in data-type. And another plus is readability.

Upvotes: 3

Views: 5803

Answers (1)

Ronen Ariely
Ronen Ariely

Reputation: 2434

Good day,

My first guess is that you need user defined table type, as other mentioned. but in any case you are wrong and you can create new types that contains other types. For example I use "complex number" type which I create using SQLCLR.

You can see a simple code here: http://www.codeproject.com/Articles/15651/Creating-User-Defined-Data-Types-in-SQL-Server

this is not the code that I use :-) I just find this on Google now.

To clarify, the "complex number" is build from 2 simple decimal numbers, that mean we use two types to create out user defined data type :-)

I use other types as well for example another one is BIGBIGINT. I have a very big table which need integers bigger than BIGINT... I created another type BIGBIGINT which use two BIGINT types.

I hope this useful :-)

Upvotes: 5

Related Questions