CODEWITHSUNDEEP
sqlsql-serverstored-procedures
ChrisOPeterson
ChrisOPeterson

Reputation: 559

Mix Create Stored Procedure and Insert Statements in Sql Server

I created a number of scripts to be run separately but was asked to combine them all so the DBA only has to do it once. The problem is that I cannot seem to combine them to run together. Only the first item in the query gets run. How do I format these to run together in one big script?

   USE [DEV]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Create PROCEDURE [dbo].[Projects]
       @ProjectID            int,
       @ClientID             int
    AS
    BEGIN
        .....Cool procedure here
    END
   GRANT EXECUTE ON [dbo].[Projects] TO Admin, Employee

INSERT INTO random_table(stuff)
VALUES (stuff)

Upvotes: 0

Views: 771

Answers (4)

code master
code master

Reputation: 2026

USE [DEV]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Create PROCEDURE [dbo].[Projects]
       @ProjectID            int,
       @ClientID             int
    AS
    BEGIN
        .....Cool procedure here
    END
    GO //add Go after every statement
   GRANT EXECUTE ON [dbo].[Projects] TO Admin, Employee
   GO
   INSERT INTO random_table(stuff)
   VALUES (stuff)

Although, I would recommend you to generate script of Database schema (including Stored procedures, functions, table creation, insertion, updation and deletion) from your SQL server database and save it with .SQL file and you don't have to place these GOs manually. Take a look at this fine example

Upvotes: 0

Yada
Yada

Reputation: 31225

Insert GO after each statement.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

Add a GO between statements

USE [DEV]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Create PROCEDURE [dbo].[Projects]
       @ProjectID            int,
       @ClientID             int
    AS
    BEGIN
        .....Cool procedure here
    END

   GO -- Add GO here

   GRANT EXECUTE ON [dbo].[Projects] TO Admin, Employee

   GO -- Add GO here
INSERT INTO random_table(stuff)
VALUES (stuff)

Upvotes: 4

David
David

Reputation: 73564

   USE [DEV]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Create PROCEDURE [dbo].[Projects]
       @ProjectID            int,
       @ClientID             int
    AS
    BEGIN
        .....Cool procedure here
    END
   GRANT EXECUTE ON [dbo].[Projects] TO Admin, Employee
GO -- added this "go" statement
INSERT INTO random_table(stuff)
VALUES (stuff)

Upvotes: 2

Related Questions