The GOTO statement causes the execution of the T-SQL batch to stop processing the following commands to GOTO and processing continues from the label where GOTO points.
GOTO statement can be used anywhere within a procedure, batch, or function. GOTO can be nested as well. GOTO can be executed by any valid user on SQL SERVER. GOTO can co-exists with other control of flow statements (IF…ELSE, WHILE). GOTO can only go(jump) to label in the same batch, it can not go to label out side of the batch.
Syntax:
Define the label:
label:
ALTER the execution:
GOTO label
Notes from MSDN
I do not prefer to use GOTO (except while doing error checking sometime). Use GOTO statement sparingly. Excessive use of the GOTO statement adds difficult to understand the logic of the T-SQL batch. I almost always implemented the logic which involves GOTO with other control-of-flow statement. GOTO is best used for breaking out of deeply nested control-of-flow statements.
The label that is the target of a GOTO identifies only the target of the jump. The label does nothing to isolate the statements following it from the statements immediately before it. Any user executing the statements immediately before the label skips the label and executes the statements after the label. This happens unless the statement immediately preceding the label is itself a control-of-flow statement, such as a RETURN. (MSDN Reference)
The following is an examples of a GOTO from BOL:
GOTO Example for SQL SERVER 2005:
DECLARE @Counter INT;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN
SELECT @Counter
SET @Counter = @Counter + 1
IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.
IF @Counter = 5 GOTO Branch_Two --This will never execute.
END
Branch_One:
SELECT 'Jumping To Branch One.'
GOTO Branch_Three; --This will prevent Branch_Two from executing.
Branch_Two:
SELECT 'Jumping To Branch Two.'
Branch_Three:
SELECT 'Jumping To Branch Three.'
GOTO Example for SQL SERVER 2000:
USE pubs
GO
DECLARE @tablename sysname
SET @tablename = N'authors'
table_loop:
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(UPPER(@tablename))
EXEC ("SELECT "" + @tablename + "" = COUNT(*) FROM "
+ @tablename)
PRINT " "
END
FETCH NEXT
FROM tnames_cursor INTO @tablename
IF (@@FETCH_STATUS <> -1) GOTO table_loop
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
26 Comments. Leave new
Thanks for that. Gave me the nerve to use a GOTO in an otherwise hideously-nested set of conditions. Give the lack of structure and exception-handling in SPs, it’s a good solution.
Fantastique ,thanks
Thank U very much for giving me an idea about “goto”. This is a very good article.
How do we use a formula for the column name in SQL?
It is known as derived column. Read about it in SQL Server help file
Your face in the header of this website is very large.
please remove it
Who cares? His articles are helpful and I don’t care if he had 3 eyes and antenna, there’s no reason to be so rude. Manners don’t cost anything.
Too Funny! You are a wanker for posting that comment.. LOL still funny.
PS. Great Site! And I don’t mind your face :D
Just make sure you don’t try to use “Exit” as your label. I tried that, and couldn’t figure out the problem! “Exit” must be a reserved SQL keyword. I changed it to “Exit_Function” and it works perfect now.
I know this might seem silly to some, but I had never run across the “Exit” thing before, and its a logical name for my label.
Yes EXIT is a keyword used in osql
how to use addzero function in sql,result willbe 001,002,003 in department,001001,001002,001003 in empoloyee details
the “my views on GOTO” statement should be renamed to “msdn views on goto” as the complete paragraph has been copied from msdn and is named as “my views on goto”.
Hi Pinal,
The first paragraph is not from msdn. The paragraph with heading “my views on goto” is from msdn other then the first line – check this link https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188729(v=sql.105)
It exactly matches with what you have mentioned in paragraph “my views on GOTO” even the explanation. I do follow your blogs but this has a very bad impression on me. you have not even mentioned the BOL link from where you took the T-SQL.
“My Views on GOTO
I do not prefer to use GOTO (except while doing error checking sometime). Use GOTO statement sparingly. Excessive use of the GOTO statement adds difficult to understand the logic of the T-SQL batch. I have almost always implemented the logic which involves GOTO with other control-of-flow statement. GOTO is best used for breaking out of deeply nested control-of-flow statements.
The label that is the target of a GOTO identifies only the target of the jump. The label does nothing to isolate the statements following it from the statements immediately before it. Any user executing the statements immediately before the label skips the label and executes the statements after the label. This happens unless the statement immediately preceding the label is itself a control-of-flow statement, such as a RETURN”
This is copied as such from here https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188729(v=sql.105) and named as My view.
Hi Pinal,
Appreciated. It doesn’t matter how old a blog is. The blog should contain correct information.
Also, the heading “my views on goto” should be removed/changed as these are essentially msdn views and not yours. Giving a reference won’t be enough here.
Thanks,
Saleem
“My Views on GOTO
I do not prefer to use GOTO (except while doing error checking sometime). Use GOTO statement sparingly. Excessive use of the GOTO statement adds difficult to understand the logic of the T-SQL batch. I almost always implemented the logic which involves GOTO with other control-of-flow statement. GOTO is best used for breaking out of deeply nested control-of-flow statements.”
and the
The msdn para
“Use the GOTO statement sparingly. Excessive use of the GOTO statement can make it difficult to understand the logic of a Transact-SQL batch. The logic implemented using GOTO can almost always be implemented using the other control-of-flow statements. GOTO is best used for breaking out of deeply nested control-of-flow statements.”
Anyone can say that these are not views.. you didn’t change it correctly. I have seriously lost my faith in you.
Hi @Saleem,
Pinal is being so mellow and generous in taking time and replying your comments everytime, and accepting suggestions from you. Why are you being so rude?
It’s his blog and he can reference text from anywhere he want. MSDN does not says not to copy their text. You should keep in mind and behave while responding to people like Pinal, he has been helping the SQL community from long time and ages.
And if you have lost faith then why are you replying again & again?
Thanks,
Mnaoj
I would like to know if I can use kkk in sqlserver transactions?
I would like to know if I can use GO TO in sqlserver transactions?
Does GOTO provide a performance boost when a function has multiple IF statements? The use of GOTO would cause the script to skip to the end with the RETURN line, instead of continuing to check each IF statement’s condition.