SQL Server/T-SQL Tutorial/Transact SQL/IF — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:22, 26 мая 2010
Содержание
- 1 A script that tests for outstanding Billings with an IF statement
- 2 IF (@au_id IS NULL)
- 3 IF EXISTS
- 4 IF (SELECT COUNT(*) FROM inserted) > 1
- 5 IF (SELECT ID FROM inserted) like "99[0-9][0-9]"
- 6 If statement with aggregate function
- 7 Implementing the ELSE Statement In Our Sproc
- 8 Short circuit aborts any further processing of a logical expression as soon as its result can be determined.
- 9 The syntax of the IF...ELSE statement
- 10 Use function returned value
- 11 Use if and like to check a pattern
- 12 uses an IF...ELSE statement
- 13 Using an Expression with an Explicit Unknown Value
- 14 Using an Expression with an Unknown Value Returned from One of the Participating Simple Logical Expressions
A script that tests for outstanding Billings with an IF statement
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
IF (@au_id IS NULL)
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
IF EXISTS
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>
IF (SELECT COUNT(*) FROM inserted) > 1
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
IF (SELECT ID FROM inserted) like "99[0-9][0-9]"
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
If statement with aggregate function
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>
Implementing the ELSE Statement In Our Sproc
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
Short circuit aborts any further processing of a logical expression as soon as its result can be determined.
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
The syntax of the IF...ELSE statement
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
Use function returned value
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
Use if and like to check a pattern
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>
uses an IF...ELSE statement
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
Using an Expression with an Explicit Unknown Value
39> IF 1 = NULL
40> PRINT "TRUE"
41> ELSE
42> PRINT "FALSE or UNKNOWN"
43> GO
FALSE or UNKNOWN
Using an Expression with an Unknown Value Returned from One of the Participating Simple Logical Expressions
4> IF (1 = 1) AND (1 > NULL)
5> PRINT "TRUE"
6> ELSE
7> PRINT "FALSE or UNKNOWN"
8> GO
FALSE or UNKNOWN