|
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,@ShipCountry
25> )
26> SELECT @OrderID = @@IDENTITY
27> GO
1>
2>
3> drop table Orders;
4> GO
1>
2> drop PROC spInsertDateValidatedOrder;
3> GO
|