Unable to Modify User-Defined Table Type

sql sql-server stored-procedures sql-server-2012 user-defined-types
Aslam Jiffry picture Aslam Jiffry · Aug 4, 2015 · Viewed 14.6k times · Source

I have a SQL User-Defined Table Type. It used in many stored procedures.Now i need to change a column in that table type. I tried to drop and recreate the User-Defined Table Type.But SQL Server doesn't Allow that. It shows up following error.

Msg 3732, Level 16, State 1, Line 3
Cannot drop type 'dbo.UserDefinedTableType' because it is being referenced by object 'SP_DoSomething'. There may be other objects that reference this type.
Msg 219, Level 16, State 1, Line 3
The type 'dbo.UserDefinedTableType' already exists, or you do not have permission to create it.

How to alter the User-Defined Table Type without modifying all the Stored procedure that uses User-Defined Table Type ?

Answer

Bogdan Bogdanov picture Bogdan Bogdanov · Aug 4, 2015

You have binding in SP_DoSomething stored procedure. The type you want to change is used in that stored procedure.

You need to save script of that procedure. Drop it. Change dbo.UserDefinedTableType and create procedure again.

There is a similar post here. Check is some of the answers can help you. Answer of @norlando seems promising.

Related questions

SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More
Stored procedure EXEC vs sp_executesql difference?

I've written two stored procedure one with sp_executesql and other doesn't have sp_executesql both are executing properly same results, I didn't get what is the difference here between EXEC (@SQL) vs EXEC sp_executesql @SQL, N'@eStatus varchar(12)…

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More