How to: Query to see if stored procedure/function exists

[Former Member]

Hello, I want to write a query used on several versions of a database. The later versions have defined a stored procedure/function that I can use, (but it is missing in the earlier versions). If it exists, I want to use it, but if it doesn't, I don't want to access it from my query or my query fails.

For example:

IF EXISTS(MyStoredProcedure) THEN MyStoredProcedure() ELSE "0" AS ProcedureResult

Is this possible?

Thanks, David

[VolkerBarth · Accepted solution]

Sure:

SELECT COUNT(*) from SYS.SYSPROCEDURE SP KEY JOIN SYS.SYSUSER SU
WHERE proc_name = 'MyStoredProcedure' AND user_name = 'MyUserName';

Note, SYS.SYSPROCEDURE does list both stored functions and procedures. When using v17, you may use the new "is_deterministic" column to filter on each type.

[Former Member · Accepted solution]

Worked like a champ, thank you Volker! I ended up saving a lot of work and just copy/pasted the stored function into my query, rather than seeing if the stored function existed and post processing.

[Former Member]

You can filter to just procedures based on the value of proc_defn:

CREATE OR REPLACE FUNCTION "someowner"."f_ProcedureExists"
(
  IN @procname LONG NVARCHAR,
  IN @owner LONG NVARCHAR DEFAULT NULL
)
RETURNS BIT
NOT DETERMINISTIC
BEGIN
  -- This function returns 1 if the specified procedure exists (owned by the specified owner).
  -- If the owner is not specified, then the connected user is assumed to be the owner.
  DECLARE @returnVal BIT;
  SET @owner = ISNULL(@owner,CONNECTION_PROPERTY('userid'));
  SET @returnVal = IF EXISTS (SELECT 1 FROM SYS.SYSPROCEDURE KEY JOIN SYS.SYSUSER ON LOCATE(proc_defn,'create procedure') = 1 AND proc_name = @procname AND user_name = @owner) THEN 1 ELSE 0 ENDIF;
  RETURN @returnVal;
END;

Usage:

IF f_procedureExists('sp_myProcedure') THEN
  CALL sp_myProcedure();
END IF;