Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

How can I structure a script so that when it runs it creates multiple stored procedures at once?

I've built a scrip which is essentially this:
USE database1
GO
CREATE PROCEDURE proc1 
.
.
USE database2
GO
CREATE PROCEDURE proc1 
.
.
USE database3
GO
CREATE PROCEDURE proc1 
.
.

Open in new window

First off, when I run the above code, it doesn't like the "GO" after the "USE database" statement.  If I remove that particular "GO" I get a bit further, but then the following error occurs:
a USE database statement is not allowed in a procedure, function or trigger. Any ideas?
0
David L. Hansen
Asked:
David L. Hansen
  • 10
  • 4
  • 4
  • +1
3 Solutions
 
Steve WalesSenior Database AdministratorCommented:
Place a GO after each CREATE PROCEDURE statement.

What error are you getting when you put GO after USE database1 ?  That is the correct syntax.

Documentation on GO and what it does is here (for reference purposes): https://msdn.microsoft.com/en-us/library/ms188037.aspx

Examples in the docs put a ";" after "USE Database1".

As of SQL Server 2012 (I think) - I believe that the ; is required now.

Try "USE database1;" as well

EDIT:  Syntax conventions are documented here: https://msdn.microsoft.com/en-us/library/ms177563.aspx - semi-colon's aren't required yet but will be in future versions.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Do like this

USE database2
GO
CREATE PROCEDURE proc1
.
.
GO ------------------------ here
USE database3
GO
CREATE PROCEDURE proc1
.....
GO
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Doing that seems to successfully create the procedures (thank you) however the Incorrect syntax near 'GO' error still pops up (for every CREATE PROCEDURE in the script).
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Aneesh RetnakaranDatabase AdministratorCommented:
if you can put the sql script here, we should be able to help; otherwise we have guess
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Try it with ANY stored procedure and see what you get. Just a simple stored_proc with a GO statement separating each (all having a different USE database line)
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Here is one (even with a simple SELECT it gives the error):
USE [Jan2015]
GO
select name from sys.procedures where name = 'p_ScripToAllDBs'
GO


USE [Building]
GO
select name from sys.procedures where name = 'p_ScripToAllDBs'
GO


USE [OurTemp]
GO
select name from sys.procedures where name = 'p_ScripToAllDBs'
GO

Open in new window

0
 
David L. HansenProgrammer AnalystAuthor Commented:
I guess it didn't create the procs after all.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I don't see any error. Are you running this on sql server management studio ? also post the exact error
0
 
David L. HansenProgrammer AnalystAuthor Commented:
I am using sql server management studio 2008 R2.  The error I posted above is the exact error (I just didn't put in the Msg, Level, Line).  Here is all of it:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.

Have you executed a similar script to see?
0
 
Steve WalesSenior Database AdministratorCommented:
Have you copied this code in from another place ?  Like a Unix / Max compliant edit and maybe there are some non printable characters there that can't be seen ?

(Long shot but let's rule it out)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
GO is only a batch separator. Isn't T-SQL keyword. By default is what is used on SSMS but can be configured so check in Tools / Options if your SSMS is really using the 'GO' as batch separator.

GO.PNG
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Already checked the "GO" batch separator; yes it is "GO" still.

Has anyone actually tried the code I posted? Just try it and see if you don't also get the problem:

USE [yourdatabase1]
GO
select name from sys.tables where name = 'a_Table'
GO


[code]USE [yourdatabase2]
GO
select name from sys.tables where name = 'a_Table'
GO


USE [yourdatabase3]
GO
select name from sys.tables where name = 'a_Table'
GO

Open in new window

I'd really like to hear what any of you get.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, I've tried that code and ran without any problem.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Can you copy the code you pasted above and run that on a new sql window. if it runs, probably you have some non printable characters. You can check them using notepad++
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please confirm that all your SP's has a BEGIN..END block and the line after the END has a GO separating the creation of SP's.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Here is what I found:

When I paste the "SELECT name FROM sys.tables..." code (posted above) into a fresh new window it worked! So I thought Aneesh must be correct about the special characters.  Here is what I found in notepad++.

From Notepad++This is using "show all characters." The part above the "--------" worked (in a fresh window). The part below, did not (also, in a fresh new window). I can't see anything weird in any of the characters (just carriage return's and line feed's).
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Found something!

When I use my code-generator (p_scriptToAllDB's) the resulting text, when cut and pasted will always fail. However, if that text is first pasted into a text editor (ex. notepad++) and then cut/copied from there back into sql server (of course, having made no changes to it) it works! It works even if I put it back in the same, failed, window.

I conclude from this that some special characters are generated by sql server which are not accepted by notepad++, so they simply don't paste into the editor. That's why we see nothing but CR/LF's and why when taken out of notepad and pasted back into sql server, it works.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Talking about weird things... :o)
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Ok. I have the final analysis here and Line feed is the cure.

My script (p_scriptToAllDBs) uses char(13) for carriage returns -- fair enough. However, it only uses char(13). That create a carriage return but NO Line Feed. Notepad++ was interpreting char(13) as CR/LF, thus adding the needed Line Feed. That's why it worked when copied from notepad++! Bottom line, if you create sql code from sql server, make sure you use char(13) + char(10). That gives you the Carriage Return AND the required Line Feed!

Now it all works perfectly.  :)
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Thank you all!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now