Dropping and recreating a stored procedure if it already exists.
I have a sql script and it needs to determine if a stored procedure already exists. If it does exists, I need to replace the stored procedure. I am using Sql server 2012. My approach is to check if the stored procedure exists. If it does exists then I want to drop it and recreate it. So below is my code. I have two questions. 1. Is the code acceptable? 2. do I need to preference the stored procedure with [dbo'] ?
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINE
WHERE ROUTINE_NAME = 'MySP'
AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROCEDURE [dbo].[MySP]
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINE
WHERE ROUTINE_NAME = 'MySP'
AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROCEDURE [dbo].[MySP]
END
ASKER
Ok, thank. What if I am using 2012. Again, is my approach acceptable?
Do I need the [dbo]
Do I need the [dbo]
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By way of example :
If object_id('dbo.my_test_sp','P') is not null drop procedure dbo.my_test_sp
go
create procedure dbo.my_test_sp
as
begin
select 'Hello World'
end
go
exec dbo.my_test_sp
There may be a better trick but in 2012 it looks like you need to query the data dictionary first:
http://www.sqlservercentral.com/blogs/martin_catherall/2011/05/04/how-do-you-create-or-update-stored-procedures_2E00_/