MySQl IF NOT EXISTS, CREATE Stored procedure

Question

I come from a background with Microsoft SQl using their Server management studio. I have recently switched to mysql and am looking to create a stored procedure with the same method I use with MSSQL. I want to create a procedure if it does not exists because I prefer that to dropping if exists. Below is the syntax I would use in MSSQL. Any help would be much appreciated.

IF NOT EXISTS 
(
SELECT 
    1 
FROM 
    sysobjects WITH (NOLOCK) 
WHERE 
    [type] = 'P' AND name = 'Sproc'
)
EXEC('CREATE PROCEDURE dbo.Sproc AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE dbo.Sproc
(
    @Sproc_Params
)
AS
BEGIN
.... --Sproc code
END

Bill Karwin · Accepted Answer · 2016-07-06 20:20:23Z

1

You can look in mysql.proc to see if a procedure exists.

SELECT db, name FROM mysql.proc WHERE db = 'dbo' AND name = 'Sproc';

However, in MySQL, you cannot ALTER PROCEDURE to replace the procedure body. You can only change a few attributes of the procedure. See http://dev.mysql.com/doc/refman/5.7/en/alter-procedure.html for details.

So you will have to drop and create the procedure anyway, if you want to change its parameters or its body.

answered Jul 6, 2016 at 20:20

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQl IF NOT EXISTS, CREATE Stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related