SQL Server/T-SQL/Transact SQL/If
Содержание
Conditional Logic IF
<source lang="sql">
18> CREATE TABLE employee (emp_no INTEGER NOT NULL, 19> emp_fname CHAR(20) NOT NULL, 20> emp_lname CHAR(20) NOT NULL, 21> dept_no CHAR(4) NULL) 22> 23> insert into employee values(1, "Matthew", "Smith", "d3") 24> insert into employee values(2, "Ann", "Jones", "d3") 25> insert into employee values(3, "John", "Barrimore","d1") 26> insert into employee values(4, "James", "James", "d2") 27> insert into employee values(5, "Elsa", "Bertoni", "d2") 28> insert into employee values(6, "Elke", "Hansel", "d2") 29> insert into employee values(7, "Sybill", "Moser", "d1") 30> 31> select * from employee 32> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) emp_no emp_fname emp_lname dept_no
-------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected) 1> 2> -- Conditional Logic IF 3> 4> CREATE PROCEDURE spTableExists 5> @TableName VarChar(128) 6> AS 7> IF EXISTS(SELECT * FROM sysobjects WHERE name = @TableName) 8> PRINT @TableName + "exists" 9> GO 1> 2> EXEC spTableExists "employee" 3> GO employeeexists 1> 2> drop procedure spTableExists 3> drop table employee 4> GO 1> 2>
</source>
ELSE: execute another line of script when the condition is not met
<source lang="sql">
22> 23> CREATE TABLE employee (emp_no INTEGER NOT NULL, 24> emp_fname CHAR(20) NOT NULL, 25> emp_lname CHAR(20) NOT NULL, 26> dept_no CHAR(4) NULL) 27> 28> insert into employee values(1, "Matthew", "Smith", "d3") 29> insert into employee values(2, "Ann", "Jones", "d3") 30> insert into employee values(3, "John", "Barrimore","d1") 31> insert into employee values(4, "James", "James", "d2") 32> insert into employee values(5, "Elsa", "Bertoni", "d2") 33> insert into employee values(6, "Elke", "Hansel", "d2") 34> insert into employee values(7, "Sybill", "Moser", "d1") 35> 36> select * from employee 37> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) emp_no emp_fname emp_lname dept_no
-------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected) 1> 2> 3> -- ELSE: execute another line of script when the condition is not met: 4> 5> CREATE PROCEDURE spTableExists 6> @TableName VarChar(128) 7> AS 8> IF EXISTS(SELECT * FROM sysobjects WHERE name = @TableName) 9> PRINT @TableName + "exists" 10> ELSE 11> PRINT @TableName + "does not" 12> GO 1> 2> EXEC spTableExists "employee" 3> GO employeeexists 1> 2> drop table employee 3> drop procedure spTableExists 4> GO 1> 2>
</source>
IF and else IF
<source lang="sql">
1> 2> CREATE FUNCTION fnFirstName (@FullName VarChar(100) 3> , @FirstOrLast VarChar(5)) 4> RETURNS VarChar(100) 5> AS 6> BEGIN 7> DECLARE @CommaPosition Int 8> DECLARE @TheName VarChar(100) 9> IF @FirstOrLast = "First" 10> BEGIN 11> SET @CommaPosition = CHARINDEX(",", @FullName) 12> SET @TheName = SUBSTRING(@FullName, @CommaPosition + 2, LEN(@FullName)) 13> END 14> ELSE IF @FirstOrLast = "Last" 15> BEGIN 16> SET @CommaPosition = CHARINDEX(",", @FullName) 17> SET @TheName = SUBSTRING(@FullName, 1, @CommaPosition - 1) 18> END 19> RETURN @TheName 20> END 21> GO 1> 2> SELECT dbo.fnFirstName("Washington, George", "First") 3> GO
George (1 rows affected) 1> SELECT dbo.fnFirstName("Washington, George", "Last") 2> GO
Washington (1 rows affected) 1> 2> drop function fnFirstName 3> GO 1> 2> 3>
</source>
if and else if statement
<source lang="sql">
1> 2> 3> -- Replace the default error message and numbers with my own: 4> 5> CREATE PROCEDURE spRunSQL 6> @Statement VarChar(2000) -- Input param. accepts any SQL statement. 7> AS 8> DECLARE @StartTime DateTime 9> , @EndTime DateTime 10> , @ExecutionTime Int 11> , @ErrNum Int 12> SET @StartTime = GetDate() 13> EXECUTE (@Statement) 14> SET @ErrNum = @@Error 15> IF @ErrNum = 207 -- Bad column 16> RAISERROR 50001 "Bad column name" 17> ELSE IF @ErrNum = 208 -- Bad object 18> RAISERROR 50002 "Bad object name" 19> ELSE IF @ErrNum = 0 -- No error. Resume. 20> BEGIN 21> SET @EndTime = GetDate() 22> SET @ExecutionTime = DateDiff(MilliSecond, @StartTime, @EndTime) 23> RETURN @ExecutionTime -- Return execution time in milliseconds 24> END 25> GO 1> 2> EXEC spRunSQL "select 1 GO" 3> GO GO
1
1> 2> EXEC spRunSQL "selet 1 GO" 3> GO Msg 102, Level 15, State 1, Server sqle\SQLEXPRESS, Line 1 Incorrect syntax near "GO". 1> 2> 3> drop procedure spRunSQL 4> GO 1> 2>
</source>
IF and EXISTS function
<source lang="sql">
13> create table Billings ( 14> BankerID INTEGER, 15> BillingNumber INTEGER, 16> BillingDate datetime, 17> BillingTotal INTEGER, 18> TermsID INTEGER, 19> BillingDueDate datetime , 20> PaymentTotal INTEGER, 21> CreditTotal INTEGER 22> 23> ); 24> 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> CREATE PROC spInsertBilling 4> @BankerID int, @BillingNumber varchar(50), 5> @BillingDate smalldatetime, @BillingTotal money, 6> @TermsID int, @BillingDueDate smalldatetime 7> AS 8> IF EXISTS(SELECT * FROM Bankers WHERE BankerID = @BankerID) 9> BEGIN 10> INSERT Billings (BankerID) 11> VALUES (@BankerID) 12> END 13> ELSE 14> BEGIN 15> RAISERROR("Not a valid BankerID!",1,1) 16> RETURN -100 17> END 18> GO 1> 2> 3> drop PROC spInsertBilling; 4> GO 1> 2> 3> drop table Billings; 4> GO
</source>
If else statement
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> drop procedure spInsertDate 3> GO 1> 2> -- The ELSE Clause 3> 4> CREATE PROC spInsertDate 5> @myDate datetime = NULL 6> AS 7> DECLARE @InsertedDate smalldatetime 8> 9> IF DATEDIFF(dd, @myDate, GETDATE()) > 7 10> SELECT @InsertedDate = NULL 11> ELSE 12> SELECT @InsertedDate = CONVERT(datetime,(CONVERT(varchar,@myDate,112))) 13> INSERT INTO Employee (start_date) VALUES ( @InsertedDate) 14> GO 1> 2> EXEC spInsertDate @myDate = "5/1/1999" 3> GO (1 rows affected) 1> 2> EXEC spInsertDate @myDate = "10/10/2006" 3> GO (1 rows affected) 1> 2> 3> select * from Employee 4> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
NULL NULL NULL NULL NULL NULL
NULL NULL NULL 2006-10-10 00:00:00.000 NULL NULL
(11 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
If statement
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> 3> -- Control-of-Flow Statements 4> 5> CREATE PROC spInsertDate 6> @myDate datetime = NULL 7> AS 8> IF DATEDIFF(dd, @myDate, GETDATE()) > 7 9> SELECT @myDate = NULL 10> INSERT INTO Employee (start_date) VALUES (@myDate) 11> GO 1> 2> EXEC spInsertDate @myDate = "5/1/1999" 3> GO (1 rows affected) 1> 2> EXEC spInsertDate @myDate = "10/10/2006" 3> GO (1 rows affected) 1> 2> SELECT * FROM Employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
NULL NULL NULL NULL NULL NULL
NULL NULL NULL 2006-10-10 00:00:00.000 NULL NULL
(11 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
