Label affecting Control of Flow
-
It has always been my understanding that labels were strickly addressable points in the program control of flow (i.e, GOTO label), but did not, by themselves, affect flow of control. This line of thinking is supported by MS's definition and description of a label . However, when the following simple code executes, if @abc = 'A', then @flag is set to 'B', as coded. At this point control should fall out of this nested IF. However, instead, control falls down to TESTIT: and begins execution at that label.
Any explanations on why this happens?
If @abc = 'A'
set @flag = 'B'
else
if @abc = 'C'
set @flag = 'A'
else
if @abc = 'B'
GOTO TestIt
else
If @xyz = 1
set @flag = 'A'
ELSE
if @xyz = 2
set @flag = 'A'
ELSE
TESTIT: if @def = 'A'
set @flag = 'A'
ELSE
IF @def = 'C'
set @flag = 'E'
ELSE
set @flag = 'C'
-
A label is a valid identifier within TSQL, and thus a valid statement (albeit doesn't do much).
Due to the lack of encapsulation around the IF ELSE statements the compiler sees
If @abc = 'A'
set @flag = 'B'
else
if @abc = 'C'
set @flag = 'A'
else
if @abc = 'B'
GOTO TestIt
else
If @xyz = 1
set @flag = 'A'
ELSE
if @xyz = 2
set @flag = 'A'
ELSE
TESTIT:
as one block of statements, and then
if @def = 'A'
set @flag = 'A'
ELSE
IF @def = 'C'
set @flag = 'E'
ELSE
set @flag = 'C'
as another block of statements.
When using IF ELSE the syntax is
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
As you have not defined a statement block using BEGIN ... END statements, the "TESTIT:" label is the sql_statement the follows the ELSE in the first block, not the "if @def = 'A'" as expected.
To get around the problem you could write the following
If @abc = 'A'
set @flag = 'B'
else
if @abc = 'C'
set @flag = 'A'
else
if @abc = 'B'
GOTO TestIt
else
If @xyz = 1
set @flag = 'A'
ELSE
if @xyz = 2
set @flag = 'A'
ELSE
begin
TESTIT:
if @def = 'A'
set @flag = 'A'
ELSE
IF @def = 'C'
set @flag = 'E'
ELSE
set @flag = 'C'
end
but you should try and be consistant when using BEGIN ... END blocks, so you could use statement blocks for the whole statement
If @abc = 'A'
begin
set @flag = 'B'
end
else
begin
if @abc = 'C'
begin
set @flag = 'A'
end
else
begin
if @abc = 'B'
begin
GOTO TestIt
end
else
begin
If @xyz = 1
begin
set @flag = 'A'
end
ELSE
begin
if @xyz = 2
begin
set @flag = 'A'
end
ELSE
begin
TESTIT:
if @def = 'A'
begin
set @flag = 'A'
end
ELSE
begin
IF @def = 'C'
begin
set @flag = 'E'
end
ELSE
begin
set @flag = 'C'
end
end
end
end
end
end
end
but this is not much fun or very readable (and it gets confusing while trying to write it too) so another way could be to use SELECT CASE, which could look something like this.
SELECT @flag = CASE WHEN @abc = 'A' THEN 'B'
WHEN @abc = 'C' THEN 'A'
WHEN @abc = 'B' THEN (SELECT CASE WHEN @def = 'A' THEN 'A'
WHEN @def = 'C' THEN 'E'
ELSE 'C' END)
WHEN @xyz = 1 OR @xyz = 2 THEN 'A'
WHEN @def = 'A' THEN 'A'
WHEN @def = 'C' THEN 'E'
ELSE 'C'
END
There are no doubt more methods (and some of the more frequent posters here are likely to come to the challenge) that are more efficient and far easier to read.
-
I would recommend to follow Nick's suggestion and convert it to CASE statement. CASE works very much like IF...ELSE, because it checks the WHEN conditions one by one, in the specified order, and as soon as data is matching that condition, check stops and the corresponding value is returned. See Books Online (= SQL Server Help) for more info about CASE.
-
Nice example, I have been indoctrinated not to use GOTO's at all but rules a made to be broken.
-
Thank you, it took me a while to get it all nicely lined up
.I haven't used a GOTO since I last programmed my Commodore64 around 15 years ago.
What I wanted to follow up with, is something that caught me out in the middle of a data conversion that I worked on. We were sourcing data from SQL Server, and exporting to text files. As these things do, during one run I found that table build was failing miserably. This was due to not using BEGIN .. END statements with my IF .. ELSE. What had happened was
IF <true>
<do something>
ELSE
-- explain why this something else is necessary
<do something else>
Unfortunately, even though the SQL Server is not supposed to evaluate the comment, it is still classed as a valid sql_statement, and thus is accepted to be the statement directly following the ELSE. It also meant the <do something else> statement was executed every time, rather than just when expected.
And finally, BSB, if you've read this far then the following from BOL (Control-Of-Flow) may help explain:
Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur. Control-of-flow language permits statements to be connected, related to each other, and made interdependent using programming-like constructs.
Essentially, SQL Server will quite happily accept a complete script as a single line of text and evaluate it as it sees each distinctly formed sql_statement.
-
Thanks, Nick and everyone, for offering advice, help, and opinions. It seems that Begin..Ends are safety precautions for microsoft bugs. I think it is a pain to have to use a block around a single statement, but I throw in lots of comments in my code so I guess I will get used to typing them in to avoid the problem Nick had in his conversion program.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply