It may look verbose, but here it is:
create procedure drop_procedure_if_exist (in pname varchar)
{
declare stmt varchar;
declare mdta_out any;
declare res_vec_out, param_vec any;
declare sql_state, err_msg varchar;
declare num_cols_out integer;
stmt := 'SELECT * from SYS_PROCEDURES where p_name like ?';
sql_state := '00000';
param_vec := vector(sprintf('%S', pname));
exec (stmt, sql_state, err_msg, param_vec, num_cols_out, mdta_out,
res_vec_out);
if (sql_state <> '00000')
signal (sql_state, err_msg);
if (length (res_vec_out) > 0) {
exec (sprintf ('drop procedure %s', pname), sql_state, err_msg,
vector(), 1, mdta_out, res_vec_out);
if (sql_state <> '00000')
signal (sql_state, err_msg);
}
}
On Tue, Apr 21, 2015 at 5:44 PM, Gang Fu <gangfu1...@gmail.com> wrote:
> The rationale is the remove a stored procedure if it exists already.
> Anyone knows how to do it?
>
> Thank you very much in advance!
>
> On Sat, Apr 18, 2015 at 1:36 PM, Gang Fu <gangfu1...@gmail.com> wrote:
>
>> Thanks a lot, Marc!
>>
>> I want to create a procedure to drop a given procedure name if it exists:
>>
>> CREATE PROCEDURE drop_procedure_if_exist ( IN pname VARCHAR ) {
>> DECLARE pcount INTEGER;
>> SELECT count(*) as pcount FROM sys_procedures WHERE p_name LIKE pname;
>> IF (pcount>0)
>> DROP PROCEDURE pname;
>> }
>>
>> But I got the error:
>> *** Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074: Line 1 (line 70
>> of "(console)"):
>> in lines 70-75 of Top-Level:
>> #line 70 "(console)"
>> CREATE PROCEDURE drop_procedure_if_exist ( IN pname VARCHAR ) { DECLARE
>> pcount INTEGER; SELECT count(*) as pcount FROM sys_procedures WHERE p_name
>> LIKE pname; IF (pcount>0) DROP PROCEDURE pname; }
>>
>> Any idea why?
>>
>> Best,
>> Gang
>>
>> On Fri, Apr 17, 2015 at 8:34 AM, Marc-Antoine Parent <mapar...@acm.org>
>> wrote:
>>
>>> I have used this:
>>>
>>> SELECT COUNT(*) FROM SYS_PROCEDURES WHERE P_NAME = :name
>>>
>>> > Le 2015-04-11 à 08:45, Gang Fu <gangfu1...@gmail.com> a écrit :
>>> >
>>> > Hi,
>>> >
>>> > I want to ask how can we check the existence of a stored procedure
>>> before we drop it?
>>> > We need to drop the stored procedure before we create it, otherwise,
>>> there will be some issue, but if we drop a stored procedure that does not
>>> exist, we will get an error. So we need to check existence before drop it.
>>> >
>>> > Thank you very much!
>>> >
>>> > Best,
>>> > Gang
>>> >
>>> ------------------------------------------------------------------------------
>>> > BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
>>> > Develop your own process in accordance with the BPMN 2 standard
>>> > Learn Process modeling best practices with Bonita BPM through live
>>> exercises
>>> > http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual-
>>> event?utm_
>>> >
>>> source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF_______________________________________________
>>> > Virtuoso-users mailing list
>>> > Virtuoso-users@lists.sourceforge.net
>>> > https://lists.sourceforge.net/lists/listinfo/virtuoso-users
>>>
>>>
>>
>
------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
