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

Posted on 2009-05-12
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.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Article by: Justin
In light of the WannaCry ransomware attack that affected millions of Windows machines, you might wonder if your Mac needs protecting. Yes, it does and here is how to do it.
We take a look at some of the most common obstacles that IT teams run into as they work relentlessly to keep all the alarms and sirens from going off at once.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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