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'.

Answer 1

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

Answer 2

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.