|
Dynamic SQL, generated inside the database. The code below runs, but does nothing.
Use TruCastEngine
GO
Create Procedure dbo.TestProc As Select @@spid GO
GO
Use IO
GO
Exec sp_executesql N'USE TestDatabase'
Exec sp_executesql N'Drop Procedure dbo.TestProc'
What I'm trying to do is make a stored procedure that can drop objects from a database which is passed in as a parameter. I'd like to save it in master so it's available from all databases on the server.
All SQL object deployments at my company need to have at least 3 lines of code before the header. We require an IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = ? AND TYPE = ?) and the rest. Sometimes it needs a join condition, like if we're adding an index, it needs to look at SYSOBJECTS and SYSINDEXES. The next line is to drop the object if it exists, then GO. I'd like to replace these 3 lines with something like EXEC sp_DropIfExists 'procedure', 'name of proc', 'test database' which would save a lot of time.
Pi in the sky?
|
