GOTO Statement in Sql Server

Question

0.00/5 (No votes)

See more:

Hi,

I've some doubts in GOTO Statement in SqlServer.

SQL

DECLARE	@a	Int
DECLARE	@b	Int
SELECT	@a	=	10
SELECT	@b	=	5
BEGIN
	IF		@a>@b GOTO	Greater
	IF		@a<@b GOTO	Lesser
END
Greater:	SELECT	@a
Lesser:		SELECT	@b

While executing these statements I get both labels executed. I suppose to get the value of "@a" only.

And below piece of code I got from MSDN.
But I don't understand why the "Branch_Two:" label doesn't get executed... I'm confused....

SQL

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN 
    SELECT @Counter
    SET @Counter = @Counter + 1
    IF @Counter = 3 GOTO Branch_One  --Jumps to the first branch.
    IF @Counter = 4 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.'

Thanks in advance

Posted 13-Oct-11 19:24pm

aswathy.s.88

Updated 22-Jan-18 17:51pm

Mehdi Gholam

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

P.Salini · Answer 1 · 2011-10-13T19:48:00

Read this it will give you some idea.
http://blog.sqlauthority.com/2007/05/07/sql-server-20052000-examples-and-explanation-for-goto/[^]

In the first code snippet the value of a is > b so GOTO Greater this is executed.
so Greater: SELECT @a this will get executed. after that next line is executed which is Lesser: SELECT @b .
since there is no statement to return after executing the code in label greater, the label lesser will get executed.

In the second code snippet when @Counter becomes 3 then
IF @Counter = 3 GOTO Branch_One is executed and the cursor comes out of while loop.
under label branch one these 2 statements are there

SELECT 'Jumping To Branch One.'<br />
    GOTO Branch_Three;<br />

in the above code after select, GOTO Branch_Three; statement is there so cursor will execute the statements under label branch_three
after finishing the execution of label branch_three
no statement is there to execute.
as the cursor came out of while loop branch_Two will never get executed.

I hope you understood.

kishorekke · Answer 2 · 2011-10-13T21:02:00

Solution 2

Check below

SQL

DECLARE @a  Int
DECLARE @b  Int
SELECT  @a  =   10
SELECT  @b  =   5
BEGIN
    IF      @a>@b GOTO  Greater
    ELSE IF     @a<@b GOTO  Lesser
END
Greater:    SELECT  @a  return
Lesser:     SELECT  @b return

Posted 13-Oct-11 21:02pm

kishorekke

Updated 14-Oct-11 3:16am

v2

Comments

sachin10d 14-Oct-11 9:17am

Changed elseif line and now it works fine

sachin10d · Answer 3 · 2011-10-14T03:11:00

Try this

SQL

DECLARE	@a	Int
DECLARE	@b	Int
SELECT	@a	=	10
SELECT	@b	=	5
BEGIN
	IF		@a>@b GOTO	Greater
	IF		@a<@b GOTO	Lesser
END
Greater:	SELECT	@a
			GOTO Last;
Lesser:		SELECT	@b
			GOTO Last;
Last:

Here Last works as break; statment in C#
GOTO Last; is not added it will execute the Lesser part also

so you are getting both the outputs
above change works fine

Let me know if you have any drought

You can do in both the ways what kishorekke has done or as in the above script

GOTO Statement in Sql Server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0