Unable to change your own type on sql server.

Question

I have a custom type that I create using the following:

IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'LineItemType') BEGIN DROP TYPE dbo.LineItemType END GO CREATE TYPE dbo.LineItemType AS TABLE ( Id UNIQUEIDENTIFIER, Invoice_Id UNIQUEIDENTIFIER, Cost INT, Quantity INT, Total INT, [Description] NVARCHAR(250) ); GO 

This type is used as a parameter of one of my stored procedures, such as:

 IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'AddSomething' AND SCHEMA_NAME(schema_id) = 'dbo') BEGIN DROP PROCEDURE dbo.AddSomething END GO CREATE PROCEDURE AddSomething ... @LineItems AS dbo.LineItemType READONLY AS BEGIN ... 

My problem is that since then I decided to add some columns to my type, I updated my script above by adding a column, and expected it to just be reset and recreated when I started it, but got this error instead :

Msg 3732, Level 16, State 1, Line 4 Cannot delete type 'dbo.LineItemType' because the object 'Add Something' refers to it. There may be other objects that reference this type. Msg 219, Level 16, State 1, Line 8 The type 'dbo.LineItemType' already exists, or you do not have permission to create it.

What am I missing? How can I delete my type and recreate it anytime?

Thanks.

Answer 1

I think you need to do this in the following sequence.

• DROP ALL Procedures that use the LineItemType type.
• DROP type LineItemType.
• CREATE New type LineItemType.
• CREATE ALL procedures that use the LineItemType type.

In short, generate the CREATE and DROP scripts for all stored procedures that use the LineItemType type, and then follow the sequence above.

Answer 2

Using SSDT will solve the problem of manually resetting and re-creating link procedures, but this script should help otherwise:

 create table #tmp(name nvarchar(200), module nvarchar(max)) insert into #tmp (name, module) select distinct '['+ps.name+'].['+p.name+']', m.[definition] from sys.procedures p join sys.schemas ps on ps.schema_id=p.schema_id join sys.parameters r on r.object_id=p.object_id join sys.types t on t.user_type_id=r.user_type_id join sys.schemas ts on ts.schema_id=t.schema_id join sys.sql_modules m on m.object_id=p.object_id where ts.name='dbo' and t.name='ttCustom' --### pay attention to this line ### select * from #tmp declare procs scroll cursor for select * from #tmp open procs declare @name nvarchar(200), @body nvarchar(max) while 1=1 begin fetch next from procs into @name, @body if @@FETCH_STATUS <> 0 break set @body = 'drop procedure ' +@name exec sp_sqlexec @body end --### re-create the type here ### DROP TYPE [dbo].[ttCustom] CREATE TYPE [dbo].[ttCustom] AS TABLE (whatever nvarchar(30)) fetch first from procs into @name, @body while @@FETCH_STATUS = 0 begin exec sp_sqlexec @body fetch next from procs into @name, @body end close procs deallocate procs drop table #tmp 

I have not tested it against procedures called by other procedures, but I do not think that they will be a problem.

Answer 3

I think you need to do this in the following sequence.

• Rename the type LineItemType.

• CREATE a new type of LineItemType

3.execute aLL Procedures that use the LineItemType type.

4. DROP type LineItemType. (renamed)