Running SQL in Batch Files

[MikeVic]

Ok, so there's this batch file (DOS commands and the like) that creates stored procedures and then runs them with some command-line SQL utility.

The problem is that this batch file has many SQL statements it runs, and if one of the early ones fails, the thing keeps on running, making some of the later statements useless.

I want to stop running the batch file if there's some kind of failure when executing one of the stored procedures, but Googling has only brought me really basic information... like whether running SQL in a batch file is possible, which I know it is.

Any help here?

[Fidatelo]

Batch File SQL? Ack. I leave that crap to DB Admins

I guess first question would be what type of database, and what command-line utility is being run (ie is it SQL Server using sqlcmd)?

[cartman]

You should be able to check for a return code, and then stop if you get an unexpected result. Is this MSSQL?

[Fidatelo]

I'm going to assume SQL Server + sqlcmd and point you to: http://msdn.microsoft.com/en-us/library/ms165702.aspx

Look for ":on error [ exit | ignore ]", that seems like it has potential.

[Daimyo]

Unless this is just a one time I thing, I'd highly recommend learning vbscript for things like this.

[Fidatelo]

Originally Posted by Daimyo

Unless this is just a one time I thing, I'd highly recommend learning vbscript for things like this.

Better yet, Powershell.

[MikeVic]

The thing is already written, I've just been asked to improve the error-handling because it ends up wasting people's time when the batch doesn't kick out on the first sign of an error.

Sorry, I should've said it's using something called "isql" to run SQL statements. We're on Sybase.

For sqlcmd, this :onerror thing seems like it'll stop the current sqlcmd, but not kick out of the actual .bat file? Or at least that's how I'm reading the description. Anyway, I can't use that for isql.

[MikeVic]

Originally Posted by Fidatelo

Better yet, Powershell.

Yeah, that's what a friend suggested.

[MikeVic]

Originally Posted by cartman

You should be able to check for a return code, and then stop if you get an unexpected result. Is this MSSQL?

What I've seen done, and might be my only option if I can't find anything else... is to look for errors in the proc, and then print out a line if one is found. Then parse that file.

[Fidatelo]

I think almost any sql command utility will have a way for you to pass back a return value of some sort from the SQL being run. You might just have to check the iSQL docs. Then, in your procs, return that code when you hit errors.

[MikeVic]

Originally Posted by Fidatelo

I think almost any sql command utility will have a way for you to pass back a return value of some sort from the SQL being run. You might just have to check the iSQL docs. Then, in your procs, return that code when you hit errors.

Yeah I'm looking for it... I came across this:
ISQL Should Exit On First Error in SQL Batch - Database Forum

And one of the replies said:

But since isql doesn't provide for an automatically-called error handling routine you have to add the code yourself, ie,
add the @@error checking where you think there could be problems.

Not sure this means isql don't have an :onerror equivalent, but I'm sketchy now.

[Fidatelo]

I think they are talking about error handling in-line in the SQL, because the guy's single SQL file has a bunch of statements and he wants to fail out as soon as any one of them fails. He's trying to avoid writing the logic into the SQL script in hope some magic command in iSQL will monitor it for him, but he's out of luck.

The way I see it, you need to have proper error handling in your SQL proc(s), such that they exit out with a specific return code at any sign of error. Then you need to find the way of checking for that return code within your batch file so that it knows to not run any other procs afterwards.

[MikeVic]

Originally Posted by Fidatelo

I think they are talking about error handling in-line in the SQL, because the guy's single SQL file has a bunch of statements and he wants to fail out as soon as any one of them fails. He's trying to avoid writing the logic into the SQL script in hope some magic command in iSQL will monitor it for him, but he's out of luck.

The way I see it, you need to have proper error handling in your SQL proc(s), such that they exit out with a specific return code at any sign of error. Then you need to find the way of checking for that return code within your batch file so that it knows to not run any other procs afterwards.

Ok thanks, that makes sense then.

Yeah, I think I'll have to do it with the parsing way I mentioned earlier. I don't think I need any special command from isql to do that.

Thanks for the input everyone.