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

asked 30 Mar, 09:38

david_ching's gravatar image

david_ching
468
accept rate: 0%


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.

permanent link

answered 30 Mar, 10:16

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 31 Mar, 02:18

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.

(31 Mar, 10:17) david_ching

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;
permanent link

answered 31 Mar, 11:20

mmellon's gravatar image

mmellon
1461310
accept rate: 25%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×28

question asked: 30 Mar, 09:38

question was seen: 139 times

last updated: 31 Mar, 11:20