3> CREATE TABLE Orders (
4> OrderID int IDENTITY (1, 1) NOT NULL ,
5> CustomerID nchar (5) NULL ,
6> EmployeeID int NULL ,
7> OrderDate datetime NULL ,
8> RequiredDate datetime NULL ,
9> ShippedDate datetime NULL ,
10> ShipVia int NULL ,
11> Freight money NULL DEFAULT (0),
12> ShipName nvarchar (40) NULL ,
13> ShipAddress nvarchar (60) NULL ,
14> ShipCity nvarchar (15) NULL ,
15> ShipRegion nvarchar (15) NULL ,
16> ShipPostalCode nvarchar (10) NULL ,
17> ShipCountry nvarchar (15) NULL)
18> GO
1> create PROC spInsertDateValidatedOrder
2> @CustomerID nvarchar(5),
3> @EmployeeID int,
4> @OrderDate datetime = NULL,
5> @RequiredDate datetime = NULL,
6> @ShippedDate datetime = NULL,
7> @ShipVia int,
8> @Freight money,
9> @ShipName nvarchar(40) = NULL,
10> @ShipAddress nvarchar(60) = NULL,
11> @ShipCity nvarchar(15) = NULL,
12> @ShipRegion nvarchar(15) = NULL,
13> @ShipPostalCode nvarchar(10) = NULL,
14> @ShipCountry nvarchar(15) = NULL,
15> @OrderID int OUTPUT
16> AS
17> DECLARE @Error int
18> DECLARE @InsertedOrderDate smalldatetime
19> DECLARE @INVALIDDATE int
20> SELECT @INVALIDDATE = -1000
21> IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7 OR @OrderDate IS NULL
22> BEGIN
23> PRINT 'Invalid Order Date'
24> PRINT 'Supplied Order Date was greater than 7 days old '
25> PRINT 'or was NULL. Correct the date and resubmit.'
26> RETURN @INVALIDDATE
27> END
28> SELECT @InsertedOrderDate =
29> CONVERT(datetime,(CONVERT(varchar,@OrderDate,112)))
30> PRINT 'The Time of Day in Order Date was truncated'
31> INSERT INTO Orders VALUES(@CustomerID,@EmployeeID,@InsertedOrderDate,@RequiredDate,@ShippedDate,
32> @ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry
33> )
34> SELECT @Error = @@ERROR
35> IF @Error ! = 0
36> BEGIN
37> IF @Error = 547
38> BEGIN
39> PRINT 'Supplied data violates data integrity rules'
40> PRINT 'Check that the supplied customer number exists'
41> PRINT 'in the system and try again'
42> END
43> ELSE
44> BEGIN
45> PRINT 'An unknown error occurred. Contact your System Administrator'
46> PRINT 'The error was number ' + CONVERT(varchar, @Error)
47> END
48> RETURN @Error
49> END
50> SELECT @OrderID = @@IDENTITY
51>
52> RETURN
53> GO
1>
2>
3> DECLARE @MyIdent int
4> DECLARE @MyDate smalldatetime
5> DECLARE @Return int
6>
7> SELECT @MyDate = '1/1/1999'
8> EXEC @Return = spInsertDateValidatedOrder
9> @CustomerID = 'ALFKI',
10> @EmployeeID = 5,
11> @OrderDate = @MyDate,
12> @ShipVia = 3,
13> @Freight = 5.00,
14> @OrderID = @MyIdent OUTPUT
15>
16> IF @Return = 0
17> SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName
18> FROM Orders
19> WHERE OrderID = @MyIdent
20> ELSE
21> PRINT 'Value Returned was ' + CONVERT(varchar, @Return)
22> GO
Invalid Order Date
Supplied Order Date was greater than 7 days old
or was NULL. Correct the date and resubmit.
Value Returned was -1000
1>
2>
3> drop PROC spInsertDateValidatedOrder;
4> GO
1>
2> drop table Orders;
3> GO