Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to check if a stored procedure exists before creating it

I have a SQL script that has to be run every time a client executes the "database management" functionality. The script includes creating stored procedures on the client database. Some of these clients might already have the stored procedure upon running the script, and some may not. I need to have the missing stored procedures added to the client database, but it doesn't matter how much I try to bend T-SQL syntax, I get

CREATE/ALTER PROCEDURE' must be the first statement in a query batch

I've read that dropping before creating works, but I don't like doing it that way.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc') DROP PROCEDURE MyProc GO  CREATE PROCEDURE MyProc ... 

How can I add check for the existence of a stored procedure and create it if it doesn't exist but alter it if it does exist?

like image 830
The Shaper Avatar asked Jan 15 '10 14:01

The Shaper


People also ask

How do you check if stored procedure is executed?

Capture SQL Server Stored Procedure History with Profiler The quick and simple way to monitor the stored procedures execution is setting up a Trace with SQL Server Profiler. This is probably good for a quick check when a user runs a process in Test and you want to capture what is he/she running.

How do I drop a procedure if exists?

DROP PROCEDURE removes the definition of one or more existing procedures. To execute this command the user must be the owner of the procedure(s). The argument types to the procedure(s) usually must be specified, since several different procedures can exist with the same name and different argument lists.

Could not find stored procedure when it exists?

This error states “Could not find stored procedure 'GO'“. It simply means that the SQL Server could found the stored procedure with the name “GO“. Now, the main reason behind this error could be the misuse of the “GO” statement.


1 Answers

I realize this has already been marked as answered, but we used to do it like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.MyProc'))    exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END') GO  ALTER PROCEDURE [dbo].[MyProc]  AS   .... 

Just to avoid dropping the procedure.

like image 142
Geoff Avatar answered Sep 17 '22 12:09

Geoff