Mix Create Stored Procedure and Insert Statements in Sql Server

Question

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)

if you put print 'test' before and after your insert is anything printed? — Abe Miessler
– Abe Miessler, Commented Jan 11, 2011 at 21:10

Mikael Eriksson · Accepted Answer · 2011-01-11 21:11:23Z

4

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)

answered Jan 11, 2011 at 21:11

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ChrisOPeterson Over a year ago

Works great. Adding Go was all that was needed. Thanks.

David · Accepted Answer · 2011-01-11 21:10:33Z

2

   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)

answered Jan 11, 2011 at 21:10

David

73.7k19 gold badges134 silver badges177 bronze badges

Comments

Yada · Accepted Answer · 2011-01-11 21:12:14Z

0

Insert GO after each statement.

answered Jan 11, 2011 at 21:12

Yada

31.5k25 gold badges106 silver badges145 bronze badges

Comments

code master · Accepted Answer · 2011-01-11 21:20:34Z

0

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

edited Jan 11, 2011 at 21:20

answered Jan 11, 2011 at 21:14

code master

2,0265 gold badges31 silver badges50 bronze badges

2 Comments

ChrisOPeterson Over a year ago

This is how I would prefer as well. I was just stuck manually combining some scripts and realized I had no idea how to do it. Thanks.

code master Over a year ago

Mark it as answer, if you think it has helped yo :=)

Collectives™ on Stack Overflow

Mix Create Stored Procedure and Insert Statements in Sql Server

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related