I am going to write a few articles (tidbits) on controlling the execution flow of statements in a Transaction SQL (T-SQL) program. <\/p>\n
Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.<\/p>\n
The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program.<\/p>\n
Today, I want to talk about looping for repetition, statement blocks, and conditional execution.<\/p>\n
Some programming languages support the following constructs: <\/p>\n
Microsoft choose to implement the WHILE<\/a> construct in Transaction SQL (T-SQL). Basically, the program repeats the execution of a statement or statement block until a condition is false.<\/p>\n Statement blocks are created by wrapping code between the BEGIN<\/a> and END<\/a> keywords. The block is executed as one set of statements.<\/p>\n The IF<\/a> statement directs the program to execute a statement only if a condition is true. Any false conditions causes the statements to be skipped.<\/p>\n The example below creates a WHILE loop that counts from 1 to 100. The key part of the code is to increment the counter. Else, the program will become an infinite loop<\/a>.<\/p>\n The IF statement is inside a statement block. It only displays (PRINT<\/a>) numbers that are divisible by three.<\/p>\n The output of this program is listed below.<\/p>\n The long and short of it, most Transaction SQL (T-SQL) programs will use Control Flow Language<\/a> statements to provide direction to the database transaction engine. <\/p>\n The WHILE statement combined with a statement block (BEGIN\/END) allows a database developer to perform a operation a number of times. <\/p>\n The IF statement can be used to conditionally execute a statement. These are just some of the building blocks to write your next program. <\/p>\n\r\n--\r\n-- Show numbers divisible by 3\r\n--\r\n\r\n-- Loop counter\r\nDECLARE @VAR_CNT INT = 1;\r\n\r\n-- Count to 100\r\nWHILE (@VAR_CNT <= 100)\r\nBEGIN\r\n -- Print when modulo is zero\r\n IF (@VAR_CNT % 3) = 0\r\n PRINT @VAR_CNT;\r\n\r\n -- Increment the counter\r\n SET @VAR_CNT += 1;\r\nEND;\r\nGO\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n\r\n3\r\n6\r\n\r\n(...)\r\n\r\n96\r\n99\r\n\r\n<\/pre>\n<\/p>\n