5>
6>
7> CREATE TABLE Orders (
8> OrderID int IDENTITY (1, 1) NOT NULL ,
9> CustomerID nchar (5) NULL ,
10> EmployeeID int NULL ,
11> OrderDate datetime NULL ,
12> RequiredDate datetime NULL ,
13> ShippedDate datetime NULL ,
14> ShipVia int NULL ,
15> Freight money NULL DEFAULT (0),
16> ShipName nvarchar (40) NULL ,
17> ShipAddress nvarchar (60) NULL ,
18> ShipCity nvarchar (15) NULL ,
19> ShipRegion nvarchar (15) NULL ,
20> ShipPostalCode nvarchar (10) NULL ,
21> ShipCountry nvarchar (15) NULL)
22> 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> AS
18> DECLARE @InsertedOrderDate smalldatetime
19> IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
20> BEGIN
21> SELECT @InsertedOrderDate = NULL
22> PRINT 'Invalid Order Date'
23> PRINT 'Supplied Order Date was greater than 7 days old.'
24> PRINT 'The value has been reset to NULL'
25> END
26> ELSE
27> BEGIN
28> SELECT @InsertedOrderDate =
29> CONVERT(datetime,(CONVERT(varchar,@OrderDate,112)))
30> PRINT 'The Time of Day in Order Date was truncated'
31> END
32> INSERT INTO Orders VALUES(@CustomerID,@EmployeeID,@InsertedOrderDate,@RequiredDate,
33> @ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,
34> @ShipPostalCode,@ShipCountry
35> )
36> SELECT @OrderID = @@IDENTITY
37> GO
1>
2> DECLARE @MyIdent int
3> DECLARE @MyDate smalldatetime
4>
5> SELECT @MyDate = GETDATE()
6>
7> EXEC spInsertDateValidatedOrder
8> @CustomerID = 'ALFKI',
9> @EmployeeID = 5,
10> @OrderDate = @MyDate,
11> @ShipVia = 3,
12> @Freight = 5.00,
13> @OrderID = @MyIdent OUTPUT
14>
15> SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName
16> FROM Orders
17> WHERE OrderID = @MyIdent
18> GO
The Time of Day in Order Date was truncated
(1 rows affected)
OrderID CustomerID EmployeeID OrderDate ShipName
----------- ---------- ----------- ----------------------- ----------------------------------------
1 ALFKI 5 2008-08-18 00:00:00.000 NULL
(1 rows affected)
1>
2>
3> drop PROC spInsertDateValidatedOrder
4> GO
1>
2> drop table orders;
3> GO