Conditional statements are used to control the flow of
execution based on condition, which is either true or false. When condition
evaluated to true the next statement will get executed, if not the statement in
else section will get its chance. Conditional statements are used much in store procedure to execute different sections of code to return different result
sets based on user input.
Suppose a user execute a store procedure “dbo.getProducts”
to query certain product or range of products from “Northwind” database as
return result set. Products or Product will only return, if user specify some
query for it otherwise he get nothing and store procedure skip its rest of
statements, which contains logic to get that certain product or range of
products provided by user.
Those conditional statements would not only cast lot money
but also do a huge bad impact to your application performance and everything in
its way, if not used carefully. In databases we are dealing with gigantic data,
which become a nice full-size problem, so not mess with conditional statements.
Basic skeleton of conditional statement in t-sql;
IF condition
PRINT 'This is the code executed when true.'
Another
version of it
IF condition
PRINT 'This is the code executed
when true.'
ELSE
PRINT 'This is the code executed
when false.'
In following example, since the code is simple and condition
stays always true so first print statement will get executed, but in store procedure conditions are evaluated relative to user input.
-- declare
variable @var of type int, to store integers
DECLARE @var INT
--
initialize @var variable to 1
SET @var = 1
--
evaluation of condition, which is true
IF @var = 1
PRINT 'This is
the code executed when true.'
ELSE
PRINT 'This is
the code executed when false.'
Condition and BEGIN. . .END
You will get in trouble when you want to execute an entire
block of code conditionally because an “IF” statement executes only the next
line of code base on evaluated condition. T-SQL provides us a solution in form
of “BEGIN. . .END” statement to allow our code blocks to be executed as a unit.
It is strongly recommended that you always use a BEGIN. . .END with an IF even
when you are going to execute only a single line of code conditionally.
-- declare
variable @var of type int, to store integers
DECLARE @var INT
--
initialize @var variable to 1
SET @var = 1
--
evaluation of condition, which is true
IF @var = 1
BEGIN
PRINT 'This is
the code executed when true.'
PRINT 'This code
is also executed only when the condition is true.'
END
ELSE
BEGIN
PRINT 'This is
the code executed when true.'
PRINT 'This code
is also executed only when the condition is true.'
END
