Implementing the ELSE Statement In Our Sproc : IF « Transact SQL « SQL Server / T-SQL Tutorial






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








20.7.IF
20.7.1.The syntax of the IF...ELSE statement
20.7.2.Using an Expression with an Explicit Unknown Value
20.7.3.Using an Expression with an Unknown Value Returned from One of the Participating Simple Logical Expressions
20.7.4.Short circuit aborts any further processing of a logical expression as soon as its result can be determined.
20.7.5.IF (@au_id IS NULL)
20.7.6.IF EXISTS
20.7.7.Use function returned value
20.7.8.IF (SELECT ID FROM inserted) like '99[0-9][0-9]'
20.7.9.IF (SELECT COUNT(*) FROM inserted) > 1
20.7.10.If statement with aggregate function
20.7.11.Use if and like to check a pattern
20.7.12.Implementing the ELSE Statement In Our Sproc
20.7.13.A script that tests for outstanding Billings with an IF statement
20.7.14.uses an IF...ELSE statement