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.ROUTINES
WHERE ROUTINE_NAME = 'MySP'
AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE [dbo].[MySP]