Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Location
    London
    Posts
    16

    Unanswered: Drop procedure if exists

    Im trying to create a small script that will drop a stored procedure if it exists. Im using this script:

    begin atomic
    if ( 1= ( select count(procname) from syscat.procedures where definer= user and procname = 'PR_GETNEXTSEQNUMBER' ) )
    DROP PROCEDURE BlakeyM.PR_GetNextSeqNumber;
    end if;
    end
    @

    The error I get when I run this is:


    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "begin atomic if ( 1= ( select count(procnam"
    was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
    "<space>". LINE NUMBER=2. SQLSTATE=42601

    Any ideas what im doing wrong.

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    Try this out:

    CREATE PROCEDURE DROPPROC()
    LANGUAGE SQL
    SPECIFIC DROPPROC
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    declare stmt varchar(200);
    if ( 1= (select count(procname) from syscat.procedures where definer= 'USER' and procname = 'PR_GETNEXTSEQNUMBER' )) Then
    set stmt = 'DROP PROCEDURE PR_GETNEXTSEQNUMBER';
    EXECUTE IMMEDIATE stmt;
    end if;
    END P1

    Let me know if it works for you.

    Cheers,
    Prashant

  3. #3
    Join Date
    Feb 2003
    Location
    London
    Posts
    16
    No this didnt work. I added:

    call dropProc() to the end and this didnt work either. Is there no way to mix procedural logic (ie IF - ELSE) within a standard sql statement. In oracle this is known as an anonymous pl/sql block.

  4. #4
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    Which RDBMS are you working on and what is the OS?

    This works perfectly for me on DB2 7.2 on Windows 2000 Professional.

    Cheers,
    Prashant

  5. #5
    Join Date
    Feb 2003
    Location
    London
    Posts
    16
    Im running personal edition 8.1 on windows 2000

  6. #6
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Then it should definitely work.
    Can you send the error that you are getting.

    Cheers,
    Prashant

  7. #7
    Join Date
    Feb 2003
    Location
    London
    Posts
    16
    Thanks dahalkar_p

    I finally manged to get it to work by removing the single quotes from
    definer= 'USER'

    Do you know of a way to do the same thing without actually creating a stored procedure?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •