Unable to change my custom type in sql server

Question

I have a custom type that I create by 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 like this:

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 I have since decided to add some columns to my type, I updated my script above adding the column, and expected it to simply drop and re-create when I ran it but instead I got this error:

Msg 3732, Level 16, State 1, Line 4 Cannot drop type 'dbo.LineItemType' because it is being referenced by object 'AddSomething'. 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 drop my type and recreate it at any time?

Thanks.

Answer 1

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

1. DROP ALL Procedures that use the LineItemType Type.
2. DROP The LineItemType Type.
3. CREATE The New LineItemType Type.
4. CREATE ALL the Procedures that use the LineItemType Type.

In short, generate the CREATE and DROP Scripts for all the Stored Procedures that use the LineItemType Type and then follow the above sequence.

Answer 2

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

1. Rename the LineItemType Type.

2. CREATE the New LineItemType Type

3.execute aLL Procedures that use the LineItemType Type.

4. DROP the LineItemType Type.(Renamed)