0

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.

Community
  • 1
  • 1
UserEsp
  • 415
  • 1
  • 7
  • 29

1 Answers1

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...
mirtheil
  • 8,952
  • 1
  • 30
  • 29
  • No. Easiest way would be to create Procedures that drop the trigger / function if it finds them. You'd need to change the SELECT query to look for the function and trigger. – mirtheil May 09 '16 at 19:22