Sql - goto statement

Question

Is it good practice to use 'goto' statements in SQL queries?

Answer 1

Depends on the SQL - some of the dialects don't provide a useful mechanism for flow control other than GOTO.

GOTO is generally bad form.

Answer 2

Not in production code but for testing could be ok.

For example, wanting to provide regression testing for a stored procedure where the "common bit" is the call to the procedure being tested and debug statements.

declare @test int;
set @test = 1;
goto tests

common:
print 'common bit'

tests:
if @test = 1 print '1';
if @test = 2 print '2';
if @test = 3 print '3';

set @test = @test + 1;
if @test <= 3 goto common
print 'finished ' + cast(@test as varchar(5))
go  -- goto can not be used past go!

Being a t-sql noob I was hoping for procedure or function to declare within scope to do the "common bit" but this was the best I could come up with after much googling. Why would you have to setup a stored procedure for every bit of code you want to re-use. Especially for non production work.

Answer 3

No.

As with other languages, there's almost always a better option to use than a Goto.

If you tell us which SQL Package you're using and what you're trying to accomplish, we might be able to give you an idea as to exactly which might fit.