Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Altering a stored procedure

    Is there a way to alter an already compiled stored procedure in sybase? The only way to do it seems to drop the SP and create it again. I am looking for an equivalent of REPLACE(in Oracle) or ALTER(in SQL Server) in sybase.

    thanks,
    Snigdha

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya,

    Oracle uses REPLACE to change procedure code without drop and re-create. It also has a replace function for string manupulation.

    If you are after the former then sp_recompile in Sybase does pretty much the same.

    String manipulation can be done using the stuff() in Sybase. Say you want to stuff Microsoft SQL and replace it with ASE.
    For Eg:
    select stuff(stuff("MS SQL",1,3,"A"),3,2,"E")

    ---
    ASE
    Last edited by willy_and_the_ci; 04-27-04 at 21:18.

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Unhappy

    Can only tables be recompiled using sp_recompile in sybase ? I am getting the following error, while trying to recompile my stored proc (DUMMY)

    exec sp_recompile DUMMY

    Server Message: Number 17761, Severity 16
    Object 'DUMMY' is not a table.
    (1 row affected)
    (return status = 1)

  4. #4
    Join Date
    Jan 2004
    Posts
    19
    you can recompile a stored proc. in sybase(for that stored proc. only) by using
    exec <sored_proc_name> with recompile

    however this will create an additional query plan(potentially different) for the stored proc in the procedure cache.

    I am not aware what replace or text does but if you want to change stored proc. text in sybase you will have to drop and recreate the stored proc.
    sp_recompile/with recompile options are used for generating a new query plan in sybase.

  5. #5
    Join Date
    Nov 2003
    Posts
    52

    Thumbs up

    Hi i was looking for something similar and i had found sp_recompile sp, and according to documentation the sp will be recompiled untill the next time you execute it

Posting Permissions

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