How to run multiple drop proc XXXX create proc XXXX in one script

Posted on 2009-05-12
Medium Priority
Last Modified: 2012-05-06

I have made some changes to a set of stored procedures and want to drop the old versions and create the new versions all in one go.

Currently I have to run each statement independently which means I could make a mistake or miss a statement.

What can I do to automate this?

I have tried putting semi-colons on the end of each drop and each create however this is complained at when I attempt to execute it.

I have also tried putting GO after each statement however this gets a similar error as the semi-colon.
drop proc getDocuments;
create proc getDocuments(parameters etc)
**code in here
drop proc getDocuments
create proc getDocuments(parameters etc)
**code in here

Open in new window

Question by:Simonss
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 19

Expert Comment

ID: 24367653
Which Sybase product and version and what tool are you using to execute the SQL?


Author Comment

ID: 24367833
I'm using sybase 15 and im using two tools to execute, AnySQL which uses the ASE15 ODBC Driver and Interactive SQL, which is a Sybase app.
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

LVL 19

Expert Comment

ID: 24367990
Several things.

First, forget the semi colons.  That is a Sybase ASA syntax and is not supported by Sybase ASE which is what you are using.

Second, using ISQL, your "go" statements should be on there own line, in the first column, and be in LOWER case.  Sybase is, by default case sensitive and ISQL is always so.

Third, it is best practice to test for the existence of an object before dropping it so that you can run the script under any conditions and not get errors.  Something along the lines of...

IF OBJECT_ID('dbo.get_documents') IS NOT NULL
    DROP PROCEDURE dbo.get_documents
CREATE PROCEDURE get_documents.....

Finally, your syntax for the CREATE PROCEDURE statements looks more like ASA than ASE and should probably be cleaned up.  Remove the parenthesis from the argument list and add and "AS" before the begin...

CREATE PROCEDURE get_documents @doc_name varchar(64), @author varchar(64)


Author Comment

ID: 24372639
Hi Bill,

Thanks for your comments.  I didn't realise there was syntax differences between ASA and ASE.  As you can probably guess I'm pretty new to sybase.

So i dropped the semicolons and put the go statements after each of my stored procedures and also added the code to check if they exist before dropping them.  Thanks for that, very useful.

I actually have three clients that I can use to execute against the sybase database.

Microsoft iSQL/w which appears to be version 'SQL6.5'
Interactive SQL which is part of SQL Anywhere 11
AnySQL Maestro

Using Microsoft iSQL/w (which is an old, and it my eyes pretty poor client) the code worked perfectly.

// Using SQL Anywhere Interactive SQL I get the error
Could not execute statement.
The 'DROP PROCEDURE' command is not allowed within a multi-statement transaction in the 'DATABASE_NAME' database
SQLCODE=7718, ODBC 3 State="ZZZZZ"

//Using AnySQL Maestro
[Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near 'go'

I'm guessing this has something to do with the way each of these applications uses the ODBC Driver.  Can you shed any light on why this only works in the old archaic application rather than the two newer applications?

LVL 19

Accepted Solution

grant300 earned 300 total points
ID: 24376329
I would toss Microsoft iSQL/w.  It is not a Sybase product and does not support all the features and functionality of ASE 15.  It is somewhat amazing that ASE actually recognized the old dbLib version of TDS and worked at all.

Toss Interactive SQL / SQL Anywhere 11 as that is a different product with different feature sets.  Again, you might as well try and use ODBC drivers for DB2 to connect to Oracle (O.K. that is an exageration but you get my point).  Bottom line, you have to use the ASE 15 ODBC drivers are you will have problems.

Install the full ASE 15 client for Windows.  You will get Sybase Central, JISQL (a Java Windowing ISQL tool), and command line ISQL.  These all use JDBC / ctlib to connect so you don't have to fool with trying to make the wrong ODBC drivers work.

After that, you can find a whole host of free-ware and/or low cost tools for doing database development.  My "best value" product is Aquafold Aqua Data Studio.  It is $395 but is worth every penny as you get an interactive debugger with it AND it will work just as well with SQL Anywhere and SQL Server as well as a whole list of other databases.

Another alternative is Sybase Workspace.  It is an Eclipse-based tool that has a debugger as well.  Right now, it is free so you might want to jump on that bandwagon.

In any event, you are going to want a full blown database IDE.  They do things like generate the DROP PROCEDURE statements automatically for you and have lots of other really great time-saving features.


Author Comment

ID: 24624465

Thanks for your comments.  In the end my problem lay mainly with the tools I was using to connect to the DB.

It turns out we have site wide licenses for DBArtisan and Rapid SQL.  These appear to be pretty good and using 'go' I have been able to delete/update my stored procs using one execution.

Thanks for all your input, you pointed me in the right direction and clarified a few points for me that have helped me out.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
The Summer 2017 Scholarship Winners have been announced!
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question