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 |
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. 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 '17, 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; |