Db2 Sql Select For Update Example' title='Db2 Sql Select For Update Example' />T SQL Programming Part 1 Defining Variables, and IF. ELSE logic Database. Journal. com. This is the first of a series of articles discussing various aspects of T SQL programming. E15586_01/integration.1111/e10231/img/dbsp_db2demo2.gif' alt='Db2 Sql Select For Update Example' title='Db2 Sql Select For Update Example' />Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T SQL programming. This first article will discuss defining variables, and using the IF. ELSE logic. Local Variables. As with any programming language, T SQL allows you to define and set variables. A variable holds a single piece of information, similar to a number or a character string. Variables can be used for a number of things. Here is a list of a few common variable uses To pass parameters to stored procedures, or function. To control the processing of a loop. To test for a true or false condition in an IF statement. To programmatically control conditions in a WHERE statement. In SQL Server a variable is typical known as a local variable, due the scope of the variable. The scope of a local variable is only available in the batch, stored procedure or code block in which it is defined. A local variable is defined using the T SQL DECLARE statement. The name of the local variable needs to start with a sign as the first character of its name. A local variable can be declared as any system or user defined data type. Here is a typical declaration for an integer variable named CNTDECLARE CNT INTMore than one variable can be defined with a single DECLARE statement. To define multiple variables, with a single DECLARE statement, you separate each variable definition with a comma, like soDECLARE CNT INT, X INT, Y INT, Z CHAR1. Above I have defined 4 local variables with a single DECLARE statement. A local variable is initially assigned a NULL value. A value can be assigned to a local variable by using the SET or SELECT statement. On the SET command you specify the local variable and the value you wish to assign to the local variable. Here is an example of where I have defined my CNT variable and then initialize the variable to 1. DECLARE CNT INT. Here is an example of how to use the SELECT statement to set the value of a local variable. DECLARE ROWCNT int. SELECT ROWCNTCOUNT FROM pubs. The above example sets the variable ROWCNT to the number of rows in the pubs. One of the uses of a variable is to programmatically control the records returned from a SELECT statement. You do this by using a variable in the WHERE clause. Here is an example that returns all the Customers records in the Northwind database where the Customers Country column is equal to GermanyDeclare Country varchar2. Country Germany. Company. Name from Northwind. Customers. where Country Country. IF. ELSET SQL has the IF statement to help with allowing different code to be executed based on the results of a condition. The IF statement allows a T SQL programmer to selectively execute a single line or block of code based upon a Boolean condition. There are two formats for the IF statement, both are shown below Format one IF lt condition lt then code to be executed when condition true Format two IF lt condition lt then code to be executed when condition true                     ELSE lt else code to be executed when condition is false In both of these formats, the lt condition is a Boolean expression or series of Boolean expressions that evaluate to true or false. COBOL, DB2, SQL, insert update delete table, Embedded SQL, Model Program. Native DB2 LUW database snapshot utilities identify performance characteristics of the database at the moment they are run, but are not easily used to identify. Are you curious about how you can maximize the XMLTABLE function in SQLXML Do you want to learn how to retrieve XML data in a relational format This article. After-Update-with-Inner-Join.png' alt='Db2 Sql Select For Update Example' title='Db2 Sql Select For Update Example' />If the condition evaluates to true, then the then code is executed. For format two, if the condition is false, then the else code is executed. If there is a false condition when using format one, then the next line following the IF statement is executed, since no else condition exists. The code to be executed can be a single TSQL statement or a block of code. If a block of code is used then it will need to be enclosed in a BEGIN and END statement. Lets review how Format one works. This first example will show how the IF statement would look to execute a single statement, if the condition is true. Here I will test whether a variable is set to a specific value. If the variable is set to a specific value, then I print out the appropriate message. Declare x int. The number is 2. The number is 3. 0. The above code prints out only the phrase The number is 2. IF statement evaluates to true. Since the second IF is false the second print statement is not executed. Now the condition statement can also contain a SELECT statement. The SELECT statement will need to return value or set of values that can be tested. If a SELECT statement is used the statement needs to be enclosed in parentheses. Pubs. Authors. where aulname like A D 0. Found A D Authors. Here I printed the message Found A D Authors if the SELECT statement found any authors in the pubs. A, B, C, or D. So far my two examples only showed how to execute a single T SQL statement if the condition is true. T SQL allows you to execute a block of code as well. A code block is created by using a BEGIN statement before the first line of code in the code block, and an END statement after that last line of code in the code block. Here is any example that executes a code block when the IF statement condition evaluates to true. Print We are in the Master Database. Print So be careful what you execute. Above a series of PRINT statements will be executed if this IF statement is run in the context of the master database. Adobe Media Encoder Error Could Not Read From The Source Table there. If the context is some other database then the print statements are not executed. Sometimes you want to not only execute some code when you have a true condition, but also want to execute a different set of T SQL statements when you have a false condition. If this is your requirement then you will need to use the IF. ELSE construct, that I called format two above. With this format, if the condition is true then the statement or block of code following the IF clause is executed, but if the condition evaluates to false then the statement or block of code following the ELSE clause will be executed. Lets go through a couple of examples. For the first example lets say you need to determine whether to update or add a record to the Customers table in the Northwind database. The decision is based on whether the customer exists in the Northwind. Customers table. Here is the T SQL code to perform this existence test for two different Customer. Ids. if existsselect from Northwind. Customers. where Customer. Id ALFKI. Print Need to update Customer Record ALFKI. Print Need to add Customer Record ALFKI. Northwind. dbo. Customers. Customer. Id LARSE. Print Need to update Customer Record LARSE. Print Need to add Customer Record LARSE. The first IF. ELSE logic checks to see it Customer. Id ALFKI exists. If it exists it prints the message Need to update Customer Record, if it doesnt exist the Need to add Customer Record is displayed. This logic is repeated for Customer. Id LARS. When I run this code against my Northwind database I get the following output. Need to update Customer Record ALFKI. Need to add Customer Record LARSE. As you can see from the results Customer. Id ALFKI existed, because the first print statement following the first IF statement was executed. Where as in the second IF statement Customer. Id LARSE was not found because the ELSE portion of the IF. ELSE statement was executed.