The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.9k311457668
accept rate: 33%

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: 151 times

last updated: 31 Mar, 11:20