Create SQL Server tables and stored procedures in one script?

Question

I have a SQL script that is setting up two database tables with their keys and constraints without any problem. I won't include the whole code but the 'skeleton' of it looks like this:

 BEGIN
CREATE TABLE [table] (

)

CREATE TABLE [table2] (

)

ALTER TABLE table...

ALTER TABLE table2....


END

I am stuck trying to add stored procedures to this script though, ideally I would like to include this all within the same script. Could someone tell me how to include the following stored procedure into the above script?

CREATE PROCEDURE Test
    @x int
AS
BEGIN
    SELECT COUNT(*)
    FROM table
END
GO

I have tried putting it towards the end of the script and have also tried with and without the BEGIN, END and GO tags but I keep getting an error that says 'incorrect syntax near PROCEDURE'.

Are you trying to create a sql script? I'm confused with "schema" word. — Amar Palsapure, Jan 06 '12 at 09:27
If you have an error when you add the `GO` to your script there must be another problem. I know you said the script was long but perhaps you could post the section causing the problem? Also, have a read of the documentation on `GO` if you are unsure of it: http://msdn.microsoft.com/en-us/library/ms188037.aspx — Tony, Jan 06 '12 at 09:37

aF. · Accepted Answer · 2012-01-06T10:17:46.517

6

Try it like this:

USE BDNAME
GO

 BEGIN
CREATE TABLE [table] (

)

CREATE TABLE [table2] (

)

ALTER TABLE table...

ALTER TABLE table2....


END


USE BDNAME
GO

CREATE PROCEDURE Test
    @x int
AS
BEGIN
    SELECT COUNT(*)
    FROM table
END

GO

edited Jan 06 '12 at 10:17

answered Jan 06 '12 at 09:28

aF.

64,980
43
135
198

Did you mean put GO at the end of table creation and before stored procedure? Gave that a try and got 'incorrect syntax near GO' – DevDave Jan 06 '12 at 09:33
this is for sybase ase, mysql, sql server, what? – aF. Jan 06 '12 at 09:43
Apologies, the problem was coming from how the script was being called, thanks for the help though – DevDave Jan 06 '12 at 10:41

score 3 · Answer 2 · answered Jan 06 '12 at 09:32

3

Instead of using BEGIN END, put GO between all your Statements like Create, Alter. Also I would like to inform you that putting GO will create blocks in your script, so if you create some local variable in one block, it is not accessible in another.

 CREATE Table Table1(
     --Your Code
 )

 GO

 CREATE PROCEDURE Test
      @x int
 AS
 BEGIN
     SELECT COUNT(*)
     FROM Table1
 END

 GO

 --Continue your script

Hope this helps.

answered Jan 06 '12 at 09:32

Amar Palsapure

9,590
1
27
46

Do you mean put GO after each code block. After doing that I got the following error: Incorrect syntax near the keyword 'CREATE'. Incorrect syntax near the keyword 'ALTER'. Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. – DevDave Jan 06 '12 at 09:36
It would be easy if you can post your actual script, may be initial code blocks – Amar Palsapure Jan 06 '12 at 09:41

Create SQL Server tables and stored procedures in one script?

2 Answers2