Using GO dynamically - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using GO dynamically

Author

Topic

Tahumars
Starting Member

15 Posts

Posted - 2009-10-28 : 17:48:42

I am running a set of queries dynamically, and would like to have GO in between them. However, I keep getting the error

quote:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'GO'.

My query is quite large, so I tested using something as simple as:

DECLARE @SQL AS VARCHAR(5000)
DECLARE @GO AS VARCHAR(2)
SET @GO = 'GO'

SET @SQL = 'USE master'
EXEC(@SQL)
EXEC(@GO)

SET @SQL = 'SELECT * FROM PRODUCTS'
EXEC(@SQL)
EXEC(@GO)

The EXEC(@SQL) lines run successfully, but I get the above mentioned error once for each time I am calling EXEC(@GO)

Is there another way of doing this or am i making a rookie mistake somewhere in there?

Thanks in advance,

Tahumars

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-28 : 18:12:11

You do not need to use GO. It's just there for Query Analyzer, sqlcmd, SSMS query window, etc... to know it's the end of a batch.

Show us a better example and we'll help you not include GO in it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Tahumars
Starting Member

15 Posts

Posted - 2009-10-28 : 18:32:33

My reason for including GO's is to break up the script into smaller pieces. My script's structure is like:

Huge UPDATE
Print('UPDATE 1 Done')
GO
Huge UPDATE
Print('UPDATE 2 Done')
GO
etc...

So that while running the entire thing might take an hour, by splitting it into smaller batches I can know using the Prints (or the "# row(s) affected" message) when each UPDATE statement has completed.

Tahumars
Starting Member

15 Posts

Posted - 2009-10-28 : 18:38:23

Just an addendum: My issue is that I'm creating these UPDATE statements dynamically. So what my script looks like is:

declare @SQL as varchar(1000)
declare @GO as varchar(2)
set @GO = 'GO'

set @SQL = 'UPDATE....'
exec(@Sql)
print('1 done')
exec(@GO)

set @SQL = 'UPDATE....'
exec(@Sql)
print('2 done')
exec(@GO)

Initially I tried doing

declare @SQL as varchar(1000)

set @SQL = 'UPDATE....'
exec(@Sql)
print('1 done')
GO

set @SQL = 'UPDATE....'
exec(@Sql)
print('2 done')
GO

But that gave me the following error:

quote:
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@SQL".

Which is odd because @SQL is deffinitely being declared. Does it need to be re-declared after every GO?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-28 : 18:42:16

You do not need GO in what you have described. The EXEC (@SQL) is all you need to execute the dynamic UPDATE statements.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-29 : 02:21:04

It you still dont understand what was suggested

declare @SQL as varchar(1000)

set @SQL = 'UPDATE....'
exec(@Sql)
print('1 done')

set @SQL = 'UPDATE....'
exec(@Sql)
print('2 done')

Madhivanan

Failing to plan is Planning to fail

Tahumars
Starting Member

15 Posts

Posted - 2009-10-29 : 10:56:50

Thanks for your attention so far, but I don't think I'm explaining myself correctly. If I run

quote:
declare @SQL as varchar(1000)

set @SQL = 'UPDATE....'
exec(@Sql)
print('1 done')

set @SQL = 'UPDATE....'
exec(@Sql)
print('2 done')

The print messages all show up at the same time when the entire script is finished. What I would like is to see the prints right after their corresponding query has completed. So assuming i have 6 updates that take 10 minutes each, instead of seeing all of the '1 done', '2 done' prints at once an hour after I start running the script, I would like to see '1 done' after the first query finishes in 10 minutes and '2 done' after the second query finishes 10 minutes after that.

So to sum up, I'm not including GO to get my queries to execute, I'm including it to get them to execute individually as batches.

Sorry, I know this is a bit convoluted and I'm not doing a great job of explaining myself.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-29 : 13:57:24

they already are individual batches. each exec() is an individual batch. this is why Tara said you don't need the GO statements

Tahumars
Starting Member

15 Posts

Posted - 2009-10-29 : 14:06:23

Hmmm, then I need to figure out why my prints are being delayed (all showing up at once at the end). I'll do some more investigation into that, I just assumed that I needed GO's. Thanks for the help everyone.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 02:30:46

quote:
Originally posted by Tahumars

Hmmm, then I need to figure out why my prints are being delayed (all showing up at once at the end). I'll do some more investigation into that, I just assumed that I needed GO's. Thanks for the help everyone.

You can't do what you are expecting. You will see prints only after the execution of all the statements. Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-10-30 : 02:53:05

instead of printing, you could insert a row into a "progress" table and then periodically select from that table in another query window to check progress.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources