Sql – Create SQL Server tables and stored procedures in one script

sqlsql serversql-scriptsstored-procedures

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

Best Answer

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
Related Topic