SQL Server/T-SQL Tutorial/Transact SQL/IF

Материал из SQL эксперт
Перейти к: навигация, поиск

A script that tests for outstanding Billings with an IF statement

   <source lang="sql">

4> 5> create table Billings ( 6> BankerID INTEGER, 7> BillingNumber INTEGER, 8> BillingDate datetime, 9> BillingTotal INTEGER, 10> TermsID INTEGER, 11> BillingDueDate datetime , 12> PaymentTotal INTEGER, 13> CreditTotal INTEGER 14> 15> ); 16> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> DECLARE @EarliestBillingDue smalldatetime 4> SELECT @EarliestBillingDue = MIN(BillingDueDate) FROM Billings 5> WHERE BillingTotal - PaymentTotal - CreditTotal > 0 6> IF @EarliestBillingDue < GETDATE() 7> PRINT "Outstanding Billings overdue!" 8> GO 1> 2> drop table Billings; 3> GO</source>


IF (@au_id IS NULL)

   <source lang="sql">

15> 16> CREATE TABLE authors( 17> au_id varchar(11), 18> au_lname varchar(40) NOT NULL, 19> au_fname varchar(20) NOT NULL, 20> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 21> address varchar(40) NULL, 22> city varchar(20) NULL, 23> state char(2) NULL, 24> zip char(5) NULL, 25> contract bit NOT NULL 26> ) 27> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE PROC prEditAuthor 3> @au_id id, 4> @au_lname varchar(40), 5> @au_fname varchar(20), 6> @phone char(12), 7> @address varchar(40), 8> @city varchar(20), 9> @state char(2), 10> @zip char(5), 11> @contract bit 12> AS 13> IF (@au_id IS NULL) BEGIN 14> INSERT INTO authors(au_lname,au_fname,phone,address,city,state,zip,contract) 15> VALUES(@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract) 16> END ELSE BEGIN 17> UPDATE authors 18> SET au_lname = @au_lname, 19> au_fname = @au_fname, 20> phone = @phone, 21> address = @address, 22> city = @city, 23> state = @state, 24> zip = @zip, 25> contract = @contract 26> WHERE au_id = @au_id 27> END 28> GO 1> 2> drop PROC prEditAuthor; 3> GO 1> 2> drop table authors; 3> GO</source>


IF EXISTS

   <source lang="sql">

7> 8> CREATE TABLE employee( 9> id INTEGER NOT NULL PRIMARY KEY, 10> first_name VARCHAR(10), 11> last_name VARCHAR(10), 12> salary DECIMAL(10,2), 13> start_Date DATETIME, 14> region VARCHAR(10), 15> city VARCHAR(20), 16> managerid INTEGER 17> ); 18> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> CREATE PROCEDURE spInsertOrUpdateEmployee 4> @FirstName nVarChar(50), 5> @LastName nVarChar(25), 6> @Salary Money 7> AS 8> IF EXISTS(SELECT * From Employee Where First_name = @FirstName) 9> UPDATE Employee SET Last_NAME = @LastName, Salary = @Salary 10> WHERE First_name = @FirstName 11> ELSE 12> INSERT INTO Employee (ID,First_Name, Last_Name, Salary) 13> SELECT 99, @FirstName, @LastName, @Salary 14> GO 1> 2> 3> 4> 5> drop table employee; 6> GO 1></source>


IF (SELECT COUNT(*) FROM inserted) > 1

   <source lang="sql">

3> CREATE TABLE authors( 4> au_id varchar(11), 5> au_lname varchar(40) NOT NULL, 6> au_fname varchar(20) NOT NULL, 7> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 8> address varchar(40) NULL, 9> city varchar(20) NULL, 10> state char(2) NULL, 11> zip char(5) NULL, 12> contract bit NOT NULL 13> ) 14> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO 1> 2> 3> CREATE TABLE publishers( 4> pub_id char(4) NOT NULL, 5> pub_name varchar(40) NULL, 6> city varchar(20) NULL, 7> state char(2) NULL, 8> country varchar(30) NULL DEFAULT("USA") 9> ) 10> GO 1> 2> 3> insert publishers values("1", "Publisher A", "Vancouver", "MA", "USA") 4> insert publishers values("2", "Publisher B", "Washington", "DC", "USA") 5> insert publishers values("3", "Publisher C", "Berkeley", "CA", "USA") 6> insert publishers values("4", "Publisher D", "New York", "NY", "USA") 7> insert publishers values("5", "Publisher E", "Chicago", "IL", "USA") 8> insert publishers values("6", "Publisher F", "Dallas", "TX", "USA") 9> insert publishers values("7", "Publisher G", "Vancouver", "BC", "Canada") 10> insert publishers values("8", "Publisher H", "Paris", NULL, "France") 11> GO 1> CREATE TABLE stores( 2> stor_id char(4) NOT NULL, 3> stor_name varchar(40) NULL, 4> stor_address varchar(40) NULL, 5> city varchar(20) NULL, 6> state char(2) NULL, 7> zip char(5) NULL 8> ) 9> GO 1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789") 2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745") 3> insert stores values("3","T","679 St.", "Portland", "OR","89076") 4> insert stores values("4","F","89 St.", "Fremont", "CA","90019") 5> GO 1> CREATE VIEW contact_list 2> AS 3> SELECT ID = au_id, name = au_fname + " " + au_lname, 4> city, state, country = "USA" 5> FROM authors 6> UNION ALL 7> SELECT stor_id, stor_name, city, state, "USA" 8> FROM stores 9> UNION ALL 10> SELECT pub_id, pub_name, city, state, country 11> FROM publishers 12> GO 1> 2> CREATE TRIGGER Insert_Contact 3> ON contact_list 4> INSTEAD OF INSERT 5> AS 6> IF @@ROWCOUNT = 0 RETURN 7> IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN 8> PRINT "Only one row at a time can be inserted" 9> RETURN 10> END 11> 12> IF (SELECT substring(ID,4,1) FROM inserted) = "-" 13> 14> INSERT into authors(au_id, au_fname, au_lname, city, state) 15> SELECT id, rtrim(substring(name, 1, charindex(" ",name) - 1)), 16> rtrim(substring(name, charindex(" ",name) + 1, 17> datalength(name) - charindex(" ",name))), city, state 18> FROM inserted 19> ELSE 20> 21> IF (SELECT ID FROM inserted) like "99[0-9][0-9]" 22> 23> INSERT INTO publishers (pub_id, pub_name, city, state, country) 24> SELECT * FROM inserted 25> ELSE 26> 27> INSERT INTO stores(stor_id, stor_name, city, state) 28> SELECT id, name, city, state from inserted 29> RETURN 30> 31> drop TRIGGER Insert_Contact ; 32> GO 1> 2> 3> drop VIEW contact_list ; 4> GO 1> 2> drop table authors; 3> drop table publishers; 4> drop table stores; 5> GO</source>


IF (SELECT ID FROM inserted) like "99[0-9][0-9]"

   <source lang="sql">

3> CREATE TABLE authors( 4> au_id varchar(11), 5> au_lname varchar(40) NOT NULL, 6> au_fname varchar(20) NOT NULL, 7> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 8> address varchar(40) NULL, 9> city varchar(20) NULL, 10> state char(2) NULL, 11> zip char(5) NULL, 12> contract bit NOT NULL 13> ) 14> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO 1> 2> 3> CREATE TABLE publishers( 4> pub_id char(4) NOT NULL, 5> pub_name varchar(40) NULL, 6> city varchar(20) NULL, 7> state char(2) NULL, 8> country varchar(30) NULL DEFAULT("USA") 9> ) 10> GO 1> 2> 3> insert publishers values("1", "Publisher A", "Vancouver", "MA", "USA") 4> insert publishers values("2", "Publisher B", "Washington", "DC", "USA") 5> insert publishers values("3", "Publisher C", "Berkeley", "CA", "USA") 6> insert publishers values("4", "Publisher D", "New York", "NY", "USA") 7> insert publishers values("5", "Publisher E", "Chicago", "IL", "USA") 8> insert publishers values("6", "Publisher F", "Dallas", "TX", "USA") 9> insert publishers values("7", "Publisher G", "Vancouver", "BC", "Canada") 10> insert publishers values("8", "Publisher H", "Paris", NULL, "France") 11> GO 1> CREATE TABLE stores( 2> stor_id char(4) NOT NULL, 3> stor_name varchar(40) NULL, 4> stor_address varchar(40) NULL, 5> city varchar(20) NULL, 6> state char(2) NULL, 7> zip char(5) NULL 8> ) 9> GO 1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789") 2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745") 3> insert stores values("3","T","679 St.", "Portland", "OR","89076") 4> insert stores values("4","F","89 St.", "Fremont", "CA","90019") 5> GO 1> CREATE VIEW contact_list 2> AS 3> SELECT ID = au_id, name = au_fname + " " + au_lname, 4> city, state, country = "USA" 5> FROM authors 6> UNION ALL 7> SELECT stor_id, stor_name, city, state, "USA" 8> FROM stores 9> UNION ALL 10> SELECT pub_id, pub_name, city, state, country 11> FROM publishers 12> GO 1> 2> CREATE TRIGGER Insert_Contact 3> ON contact_list 4> INSTEAD OF INSERT 5> AS 6> IF @@ROWCOUNT = 0 RETURN 7> IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN 8> PRINT "Only one row at a time can be inserted" 9> RETURN 10> END 11> 12> IF (SELECT substring(ID,4,1) FROM inserted) = "-" 13> 14> INSERT into authors(au_id, au_fname, au_lname, city, state) 15> SELECT id, rtrim(substring(name, 1, charindex(" ",name) - 1)), 16> rtrim(substring(name, charindex(" ",name) + 1, 17> datalength(name) - charindex(" ",name))), city, state 18> FROM inserted 19> ELSE 20> 21> IF (SELECT ID FROM inserted) like "99[0-9][0-9]" 22> 23> INSERT INTO publishers (pub_id, pub_name, city, state, country) 24> SELECT * FROM inserted 25> ELSE 26> 27> INSERT INTO stores(stor_id, stor_name, city, state) 28> SELECT id, name, city, state from inserted 29> RETURN 30> 31> drop TRIGGER Insert_Contact ; 32> GO 1> 2> 3> drop VIEW contact_list ; 4> GO 1> 2> drop table authors; 3> drop table publishers; 4> drop table stores; 5> GO</source>


If statement with aggregate function

   <source lang="sql">

3> CREATE TABLE Product( 4> ProductID int NOT NULL, 5> Name nvarchar(25) NOT NULL, 6> ProductNumber nvarchar(25) , 7> Color nvarchar(15) NULL, 8> StandardCost money NOT NULL, 9> Size nvarchar(5) NULL, 10> Weight decimal(8, 2) NULL, 11> ProductLine nchar(20) NULL, 12> SellStartDate datetime NOT NULL, 13> SellEndDate datetime NULL 14> ) 15> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> WHILE (SELECT AVG(StandardCost) FROM Product) < $1200 6> BEGIN 7> UPDATE Product SET StandardCost = StandardCost * 1.25 8> SELECT MAX(StandardCost) FROM Product 9> IF (SELECT MAX(StandardCost) FROM Product) > $4000 10> 11> BREAK 12> ELSE 13> 14> CONTINUE 15> END 16> PRINT "Done." 17> GO (10 rows affected) 

            987.2363

(10 rows affected) 

           1234.0454

(10 rows affected) 

           1542.5568

(10 rows affected) 

           1928.1960

(10 rows affected) 

           2410.2450

(1 rows affected) Done. 1> 2> 3> drop table Product; 4> GO 1> 2></source>


Implementing the ELSE Statement In Our Sproc

   <source lang="sql">

6> CREATE TABLE Orders ( 7> OrderID int IDENTITY (1, 1) NOT NULL , 8> CustomerID nchar (5) NULL , 9> EmployeeID int NULL , 10> OrderDate datetime NULL , 11> RequiredDate datetime NULL , 12> ShippedDate datetime NULL , 13> ShipVia int NULL , 14> Freight money NULL DEFAULT (0), 15> ShipName nvarchar (40) NULL , 16> ShipAddress nvarchar (60) NULL , 17> ShipCity nvarchar (15) NULL , 18> ShipRegion nvarchar (15) NULL , 19> ShipPostalCode nvarchar (10) NULL , 20> ShipCountry nvarchar (15) NULL) 21> GO 1> 2> Create PROC spInsertDateValidatedOrder 3> @CustomerID nvarchar(5), 4> @EmployeeID int, 5> @OrderDate datetime = NULL, 6> @RequiredDate datetime = NULL, 7> @ShippedDate datetime = NULL, 8> @ShipVia int, 9> @Freight money, 10> @ShipName nvarchar(40) = NULL, 11> @ShipAddress nvarchar(60) = NULL, 12> @ShipCity nvarchar(15) = NULL, 13> @ShipRegion nvarchar(15) = NULL, 14> @ShipPostalCode nvarchar(10) = NULL, 15> @ShipCountry nvarchar(15) = NULL, 16> @OrderID int OUTPUT 17> 18> AS 19> DECLARE @InsertedOrderDate smalldatetime 20> IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7 21> SELECT @InsertedOrderDate = NULL 22> ELSE 23> SELECT @InsertedOrderDate = CONVERT(datetime,(CONVERT(varchar,@OrderDate,112))) 24> INSERT INTO Orders VALUES (@CustomerID,@EmployeeID,@InsertedOrderDate,@RequiredDate,@ShippedDate, @ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountr y 25> ) 26> SELECT @OrderID = @@IDENTITY 27> GO 1> 2> 3> drop table Orders; 4> GO 1> 2> drop PROC spInsertDateValidatedOrder; 3> GO</source>


Short circuit aborts any further processing of a logical expression as soon as its result can be determined.

   <source lang="sql">

Short Circuit and Divide by Zero Using the AND Operator 8> 9> IF (0 <> 0) AND (1/0 > 0) 10> PRINT "Greater Than 0" 11> GO 1> Short Circuit and Divide by Zero Using the OR Operator 3> IF (1 > 0) OR (1/0 > 0) 4> PRINT "Greater Than 0" 5> GO Greater Than 0</source>


The syntax of the IF...ELSE statement

   <source lang="sql">

IF A=B 

  Statement when True

ELSE 

  Statement when False

To batch statements together within an IF ELSE, we must surround the code with a BEGIN�END block. An example of how the code would look follows: IF A=B 

  BEGIN
     Statement when True
     Another statement when True
     Yet Another True statement
  END

ELSE 

  False statement

6> IF 12.0 > 10E 7> PRINT "Bigger" 8> ELSE 9> PRINT "Smaller"; 10> GO Bigger</source>


Use function returned value

   <source lang="sql">

5> 6> 7> DECLARE @myresult int 8> EXEC @myresult = xp_cmdshell "dir c:\tim.txt" 9> IF (@myresult = 0) 10> PRINT "Success" 11> ELSE 12> PRINT "Failure" 13> GO Msg 15281, Level 16, State 1, Server J\SQLEXPRESS, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure "sys.xp_cmdshell" of component "xp_cmdshell" because this component is turned off as part of the security configuration for this server. A system administrator c an enable the use of "xp_cmdshell" by using sp_configure. For more information about enabling "xp_cmdshell", see "Surface Area Configuration" in SQL Server Books Online. Failure</source>


Use if and like to check a pattern

   <source lang="sql">

6> DECLARE @sql AS NVARCHAR(4000), 7> @b AS VARBINARY(1000), @s AS VARCHAR(2002); 8> SET @s = "0x0123456789abcdef"; 9> 10> IF @s NOT LIKE "0x%" OR @s LIKE "0x%[^0-9a-fA-F]%" 11> BEGIN 12> RAISERROR("Possible SQL Injection attempt.", 16, 1); 13> RETURN; 14> END 15> 16> SET @sql = N"SET @o = " + @s + N";"; 17> EXEC sp_executesql 18> @stmt = @sql, 19> @params = N"@o AS VARBINARY(1000) OUTPUT", 20> @o = @b OUTPUT; 21> 22> SELECT @b; 23> GO

0x0123456789ABCDEF

1> 2></source>


uses an IF...ELSE statement

   <source lang="sql">

4> 5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> DECLARE @MinBillingDue money, @MaxBillingDue money 4> DECLARE @EarliestBillingDue smalldatetime, @LatestBillingDue smalldatetime 5> SELECT @MinBillingDue = MIN(BillingTotal - PaymentTotal - CreditTotal), 6> @MaxBillingDue = MAX(BillingTotal - PaymentTotal - CreditTotal), 7> @EarliestBillingDue = MIN(BillingDueDate), 8> @LatestBillingDue = MAX(BillingDueDate) 9> FROM Billings 10> WHERE BillingTotal - PaymentTotal - CreditTotal > 0 11> IF @EarliestBillingDue < GETDATE() 12> BEGIN 13> PRINT "Outstanding Billings overdue!" 14> PRINT "Dated " + CONVERT(varchar,@EarliestBillingDue,1) + 15> " through " + CONVERT(varchar,@LatestBillingDue,1) + "." 16> PRINT "Amounting from $" + CONVERT(varchar,@MinBillingDue,1) + 17> " to $" + CONVERT(varchar,@MaxBillingDue,1) + "." 18> END 19> ELSE 20> PRINT "No overdue Billings." 21> GO No overdue Billings. 1> 2> drop table Billings; 3> GO</source>


Using an Expression with an Explicit Unknown Value

   <source lang="sql">

39> IF 1 = NULL 40> PRINT "TRUE" 41> ELSE 42> PRINT "FALSE or UNKNOWN" 43> GO FALSE or UNKNOWN</source>


Using an Expression with an Unknown Value Returned from One of the Participating Simple Logical Expressions

   <source lang="sql">

4> IF (1 = 1) AND (1 > NULL) 5> PRINT "TRUE" 6> ELSE 7> PRINT "FALSE or UNKNOWN" 8> GO FALSE or UNKNOWN</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Transact_SQL/IF&oldid=6349»