How can I run this sql script without using 'go'?

Question

I am trying to create a script that creates and populates tables, and then create the stored procedures for my app to access them. My script runs just fine until I get to the stored procedures. I'm trying to read a .sql file into my app and then run this script through my connection in one execution. Is there any way I can remove the 'go' keyword from this chunk of the script to make it run in one execution?

if object_id('GetStudentByID') is not null
drop procedure GetStudentByID;
go
create procedure GetStudentByID
(@StudentID INT)
as
select StudentID,FirstName,LastName
from Student
where StudentID = @StudentID;

why not just split it up to two seperate sql queries?

Hajo
– Hajo

2012-04-22 19:01:39 +00:00
Commented Apr 22, 2012 at 19:01 — Hajo
– Hajo, Commented Apr 22, 2012 at 19:01
Because there will be several stored procs like this one.

Bazinga
– Bazinga

2012-04-22 19:03:10 +00:00
Commented Apr 22, 2012 at 19:03 — Bazinga
– Bazinga, Commented Apr 22, 2012 at 19:03

RBarryYoung · Accepted Answer · 2012-04-22 19:42:35Z

5

Like This:

if object_id('GetStudentByID') is not null
    EXEC('drop procedure GetStudentByID;');

EXEC('create procedure GetStudentByID
(@StudentID INT)
as
select StudentID,FirstName,LastName
from Student
where StudentID = @StudentID;');

This is a common problem because, although it's not immediately apparent, "GO" is not a SQL Server command. Rather it is a command implemented by Management Studio (and SQLCMD as well), to allow you to tell it how to divide up a stream of SQL command text into separate compilable batches.

As SQL Server itself has no such command, you have to go about it differently in cases where you need a SQL Script to fit within a stored procedure, Agent Job, etc. The SOP way to do this is via dyanmic SQL using either sp_executeCmd(...) or EXEC('string') (as above).

edited Apr 22, 2012 at 19:42

answered Apr 22, 2012 at 19:36

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

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

Comments

Russell Hart · Accepted Answer · 2012-04-22 19:31:16Z

0

You have to have CREATE PROCEDURE in a single batch. I am not sure what you want to achieve. Do you want to execute a single command because you can't program the app to execute multiple times? If so then try to set up a loop for each procedure (post back code here if you can't get it to work :))

If you want to execute them together so they all fail or succeed together at the same point in time then wrap them in a transaction. Then you can keep the GO batch separators but they will wait until the end to commit them all.

e.g

BEGIN TRANSACTION; 
GO

if object_id('GetStudentByID') is not null
drop procedure GetStudentByID;
go
create procedure GetStudentByID
(@StudentID INT)
as
select StudentID,FirstName,LastName
from Student
where StudentID = @StudentID;
GO
/* replace this line with other procedures */

COMMIT TRANSACTION; 
GO

answered Apr 22, 2012 at 19:31

Russell Hart

1,85113 silver badges20 bronze badges

1 Comment

Bazinga Over a year ago

A loop is what I am currently using to do this. I was mainly just curious whether or not there is a way to get away from the 'go' keyword when doing this process. I'm reading in a .sql file and splitting at the 'go' keyword, and then looping. I just wanted to know if there is a cleaner way to do this or not.

Collectives™ on Stack Overflow

How can I run this sql script without using 'go'?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related