The statements in this example are used to identify if a number is large, small, or medium. The example here is that of a statement block with a nested IF…ELSE statement. The limit to the number of nested levels depends on available memory. Several IF statements can be either nested after another IF or can follow an ELSE statement. To define a statement block or to include multiple statements as part of your control-of-flow block, use the keywords BEGIN and END. Unless a statement block is used, the IF or ELSE condition may be imposed on only one Transact-SQL statement or the very next statement after the IF or ELSE. A Transact-SQL statement or statement grouping is defined by a statement block. If you execute one then the other statements in the block will be executed. Sometimes you need to treat a group of statements as a group of statements as though they were all one block of statements. PRINT ‘There are more than 15 Jeep -1000 Cars.’ĮLSE PRINT ‘There are 15 or less Jeep -1000 Cars.’ (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE ‘Jeep-1000%’ ) > 15 If there are 20 cars in the Product table that meet the WHERE clause, then the first print statement will be executed. The following example executes a query as part of a Boolean expression. If the Boolean expression contains a SELECT statement, then the SELECT statement must be enclosed in parentheses.
IF 2 = 2 PRINT ‘Boolean_expression is true.’ĮLSE PRINT ‘Boolean_expression is false.’ Example 2: Using a query as part of a Boolean expression A Boolean expression returns a TRUE or FALSE value. If the result is false then the second statement is printed. IF…Else can be used to execute a simple Boolean expression, which returns a true value when 1=1, and therefore prints the first statement. Examples and Syntax of T-SQl IF…ELSE Example 1: Using a simple Boolean expression This IF…ELSE statement is very similar to that of C Programming. When the IF condition is not satisfied and the Boolean expression returns FALSE, the optional ELSE keyword introduces another Transact-SQL statement and condition which is executed. In that case the Boolean expression returns a TRUE value. The T-SQL statement that follows an IF keyword is executed if its condition is satisfied. The IF…ELSE T-SQL statement imposes conditions on the execution of a Transact-SQL (T-SQL) statement. SELECT FROM name.db.authors IF…ELSE T-SQL Statement – What is it About? The example sets the variable to the number of columns in the name.db.authors table. You can also use the SELECT statement to set the value of a local variable. Here is an example where the CNT variable has been defined as an integer and then initialized to the value 1. A value can be assigned to a local variable by using the SET or SELECT statement. In the example below four local variables have been defined with one single DECLARE statement.Ī local variable is initially assigned a NULL value. To define multiple variables, with a single DECLARE statement, you use comma after each variable definition. You can define more than one variable with a single DECLARE statement. Here is an example of the syntax to be used to declare an integer variable named CNT. A local variable should be declared as any system or user defined data type. A local variable is defined using the T-SQL “DECLARE” statement.
The scope of a local variable is only available in the batch, stored procedure or code block in which it is defined. In an SQL Server a variable is typically known as a local variable, due the scope of the variable.
If you want to learn the fundamentals of Microsoft SQL, then the courses Microsoft SQL Database Fundamentals or Microsoft SQL Server 101 will give you the required introductory knowledge. It is a variant of the ANSI-standard Structured Query Language, SQL. A procedural language is designed to extend the abilities of SQL’s while maintaining integration with SQL. Transact-SQL or T-SQL is the proprietary procedural language that Microsoft SQL Server uses. Defining variables used in a program and understanding the IF…ELSE logic is the focus of our discussion today.
Whether you are building a stored procedure or writing a small query script you will need to know the basics of T-SQL programming.