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