I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

All we need is an easy explanation of the problem, so here it is.

Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don’t require the use of CLR)

If they do require CLR, is there an alternative I can use for my stored procedure parameter that serves the same functionality?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

User-Defined Table Types are a TSQL feature, not requiring CLR to be enabled. Nor are they CLR types "under the covers". eg

exec sp_configure 'clr enabled', 0
reconfigure with override

go

create type tableType as table(id int)

go
declare @t tableType
insert into @t(id) values (1)

select * from @t

Then to show it’s not a CLR type "under the covers" run

select * from sys.dm_clr_appdomains
go
declare @t hierarchyid =  hierarchyid::Parse('/1/1/3/')
go
select * from sys.dm_clr_appdomains

to see how there’s an AppDomain created when you create a hierarchyid but not a User-Defined Table Type.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply