AndrewMC AndrewMC - 3 months ago 13
SQL Question

Is there a way to drop a User Defined Table type that is already bound to a stored procedure?

I have an user defined table type (UDTT) that is already bound with a stored procedure which uses it as a input parameter. I have made a mistake in UDTT structure but I cannot alter UDTT so when I tried to delete it, SSMS complaints that its bound to store procedure so it cannot be deleted. Is there away to delete my UDTT?

thanks

Answer

It is required on the drop, and what I normally do is set myself a template script that follows this execution:

-- Table builds or the type

if object_id(N'dbo.<tablename>') is null
begin <create table chunk> end

-- Proc Drops

-- UDTT Drops

-- UDTT Adds

-- Proc Adds

Then if you ever need to Alter a proc, you just seek it on that script and execute the script again. Since there is no saved data in a procedure, this solves type overlooks as well as recompile options needing processed. I found it to make development as well as testing much more conducive.