[RESOLVED] SQL Server Batches

[Shaggy Hiker]

I have a modest stored procedure that creates a temporary table, populates it, then clears a permanent table and copies everything from the temporary table into the permanent table. This series of steps is performed three times for three different tables, and the whole thing takes only a few lines.

I have no particular reason to expect that any part of this will fail, but in the case of the third table, there is also a call to a different stored procedure that does the same thing for a fourth table, except that this fourth table is much more likely to fail than the other three. Failure is unlikely for any of them, though.

What I was wondering was whether there was any advantage to break the small, but monolithic, procedure up into different batches, like so:

BEGIN
DO SOMETHING WITH TABLE 1
END
GO

BEGIN
DO SOMETHING WITH TABLE 2
END
GO

etc.

What I am specifically thinking about is a way to allow the processing of tables 1 and 2 to work even if the processing of table 3 does not (if 3 fails then four can also fail...or not, I don't really care which)?

I have no experience with stored procedure behavior, so I'm wondering whether a failure of one batch will terminate the whole stored procedure, or whether it will proceed to the next batch?

[techgnome]

well, since you can't have a GO in the middle of a stored proc (at least not that I'm aware of... I've always used the GO at the end, signifying the end of the SP) it's kind of moot... but in these kinds of situations, I'll reach for a transaction... begin a transaction, do my work commit or rollback as needed, move on to the next operation.

Oddly, SQL Server does support the try/catch methodology to help...
http://msdn.microsoft.com/en-us/libr...=sql.105).aspx

BEGIN TRY
    BEGIN TRANSACTION
    -- Do Table 1 work
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH;

BEGIN TRY
    BEGIN TRANSACTION
    -- Do Table 2 work
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH;

SELECT Idea FROM Sample WHERE You='See the pattern';

Be sure to read the documentation at the link... there's going to be some relevant gotchas in there concerning transactions, try-catch,and DDL, which may be appropo ...


-tg

[TysonLPrice]

I have a modest stored procedure that creates a temporary table, populates it, then clears a permanent table and copies everything from the temporary table into the permanent table. This series of steps is performed three times for three different tables, and the whole thing takes only a few lines.

I have no particular reason to expect that any part of this will fail, but in the case of the third table, there is also a call to a different stored procedure that does the same thing for a fourth table, except that this fourth table is much more likely to fail than the other three. Failure is unlikely for any of them, though.

What I was wondering was whether there was any advantage to break the small, but monolithic, procedure up into different batches, like so:

BEGIN
DO SOMETHING WITH TABLE 1
END
GO

BEGIN
DO SOMETHING WITH TABLE 2
END
GO

etc.

What I am specifically thinking about is a way to allow the processing of tables 1 and 2 to work even if the processing of table 3 does not (if 3 fails then four can also fail...or not, I don't really care which)?

I have no experience with stored procedure behavior, so I'm wondering whether a failure of one batch will terminate the whole stored procedure, or whether it will proceed to the next batch?

If you just forgot about batches and ran:

DO SOMETHING WITH TABLE 1
DO SOMETHING WITH TABLE 2
DO SOMETHING WITH TABLE 3

And something went wrong in TABLE3 the first two would not roll back or anything like that. At least with the way the DBA group set up our environment and I'm not a DBA so I can't say for sure. It is easy enough to test something like that.

Create a couple tables:

create table table1(MyData int) 
insert into table1(MyData) values(1)

create table table2(MyData int)
insert into table2(MyData) values(2)

create table table3(MyData int)
insert into table3(MyData) values(3)

Create a stored procedure. techgnome is right about the GOs.

Create PROCEDURE [dbo].[VBForums] AS

update table1 set mydata = 5
update table2 set mydata = 5
update table3 set mydata = 'x' 

GO

Run the the storee procedure and you will get this output:


(1 row(s) affected)

(1 row(s) affected)

Msg 245, Level 16, State 1, Procedure VBForums, Line 6
Conversion failed when converting the varchar value 'x' to data type int.

The first two updates worked and the third didn't. All I'm trying to do is answer your questions. I wouldn't do something like that and how to set it up depends on the business situation. For example if table3 fails should 1 and 2 roll back? Also not using error handling when it is available to you is short sighted.

Add a fourth table

alter PROCEDURE [dbo].[VBForums] AS

update table1 set mydata = 5
update table2 set mydata = 5
update table3 set mydata = 'x' 
update table4 set mydata = 5 

GO

Three will fail and four will not get updates. You can set it up so three can fail but four still updates:

alter PROCEDURE [dbo].[VBForums] AS

update table1 set mydata = 5
update table2 set mydata = 5

BEGIN TRY
	update table3 set mydata = 'x' 
End  TRY

BEGIN CATCH
  PRINT 'Error occurred'
END CATCH

update table4 set mydata = 5 

GO

My whole point is it is easy to set up testing like this and "play around".

[Shaggy Hiker]

I'm surprised to hear that you both didn't think the GO would work, as it appeared to work fine the way I showed it, though that may have been a misunderstanding on my part, as something else was going wrong with the query so it is possible that problems with the GO....well, it never got far enough to have them. Still, the results in Management Studio made it look as if each batch was running independently of the others. I got a series of results back, all of which were wrong because of other issues, and I ended up removing all the GO statements while diagnosing the other issues, so who knows?

I wasn't aware that there was TRY CATCH in SQL. I wasn't using transactions because the situation didn't seem to benefit from them. Each batch can succeed or fail on it's own. If any one fails, the subsequent ones are virtually certain to fail, as well, so there is little reason to try them. The reason is that there is very little that can go wrong, as the actions within the batch are very straightforward. The point is to copy the data from a maddeningly complex view to a manageable table. For most of the batches, the table is based off of the same view...with some extra views added in, but all from the same DB. The actions are all pretty simple, and the views have been working for years. If one of the views fails, it is going to be one of these two reasons:

1) Somebody tinkered with it, but since the views are interrelated, if one fails the rest are suspect, at best.

2) Something has gone wrong with the server, in which case nothing is likely to work right anyways.

Therefore, the situation is rather fail tolerant. The way the batches are written, it doesn't really matter whether they succeed or not, at least not yet, but it looks like I do have a few things to look at.

[TysonLPrice]

"I'm surprised to hear that you both didn't think the GO would work"

We said in the middle of stored procedures not just straight SQL like:

select getdate()
go
select getdate()
go
select getdate()
go

"there is very little that can go wrong"

Haven't been programming long have you

Full logs
rows locked
servers down
many others..

[techgnome]

Yeah, GO won't work inside of an sproc... outside of it, it works great... and you don't need to use transactions with Try/Catch... but it could be used in a stored proc to prevent errors from throwing out the sproc and breaking your flow... In short what you need is the SQL equivalent of on error resume next.. (bleh)...

And a Try with a do-nothing Catch should be able to do that nicely.

Begin Try
-- Do table 1 stuff
End Try
Begin Catch
print 'there was an error'
End Catch

Begin Try
-- Do table 2 stuff
End Try
Begin Catch
print 'there was an error'
End Catch

...


-tg

[Shaggy Hiker]

That's what I'll do, then.

[Shaggy Hiker]

"I'm surprised to hear that you both didn't think the GO would work"

We said in the middle of stored procedures not just straight SQL like:

select getdate()
go
select getdate()
go
select getdate()
go

"there is very little that can go wrong"

Haven't been programming long have you

Full logs
rows locked
servers down
many others..

Those aren't my problem, nor will they really cause trouble for this stored procedure. It will simply fail, and that's not so bad. One of the keys to the design of the whole system is that failure of the stored procedure causes nothing more than temporary inconvenience. It will replace one thing with another thing, but only upon success. If it fails, the original thing will still be there. If it fails so badly, at just the right time, that the original thing is in a transitional state...the user is to utterly hosed in so many ways that this won't even be noticed.

[TysonLPrice]

Those aren't my problem, nor will they really cause trouble for this stored procedure. It will simply fail, and that's not so bad. One of the keys to the design of the whole system is that failure of the stored procedure causes nothing more than temporary inconvenience. It will replace one thing with another thing, but only upon success. If it fails, the original thing will still be there. If it fails so badly, at just the right time, that the original thing is in a transitional state...the user is to utterly hosed in so many ways that this won't even be noticed.

Gotcha...I was half kidding.