Unable to change my custom type in sql server
sql, sql-server
Solution
I think you have to do this in the following sequence.
- DROP ALL Procedures that use the LineItemType Type.
- DROP The LineItemType Type.
- CREATE The New LineItemType Type.
- 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.
Problem
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.