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.

score 10 · Answer 1 · answered Nov 25 '13 at 22:31

10

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.

answered Nov 25 '13 at 22:31

Shiva

20,575
14
82
112

You can automate this process, see my answer [here](http://stackoverflow.com/questions/1734541/how-to-alter-the-table-value-parameter/32422478#32422478). – BornToCode Sep 07 '15 at 10:57

score 1 · Answer 2 · answered Aug 15 '15 at 09:03

1

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

Rename the LineItemType Type.
CREATE the New LineItemType Type

3.execute aLL Procedures that use the LineItemType Type.

DROP the LineItemType Type.(Renamed)

answered Aug 15 '15 at 09:03

Farhad Manafi

334
1
8
17

score 0 · Answer 3 · answered Dec 22 '14 at 18:27

Using SSDT will solve the problem of having to drop and re-create the referencing procedures manually, 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 haven't tested it against procedures that are called by other procedures, but I don't think they'll be a problem.

Unable to change my custom type in sql server

3 Answers3