How can I check if a stored procedure exists in PERVASIVE database before creating it?

Question

I want to create a stored procedure in a Pervasive database, but only if it does not yet exist.

I found something for SQL Server:

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

I found that code on this link How to check if a stored procedure exists before creating it.

So I want something really similar for Pervasive.

Answer 1

There's no way to execute the IF NOT EXISTS syntax outside of a Stored Procedure in Pervasive. You could create a procedure taking a procedure name and drop it if it exist. Something like:

CREATE PROCEDURE DropProcIfExists(in :procname char(20))
AS
BEGIN
 IF( EXISTS (SELECT xp$Name FROM X$proc WHERE Xp$Name = :procname) ) THEN
  Exec('DROP Procedure "' + :procname + '"') ;
 END IF;
End#

call DropProcIfExists ('myProc')#

So your SQL would be something like:

call DropProcIfExists('MyNewProc')#
Create Procedure MyNewProc()
AS
BEGIN...