LISTING 2: Stored Procedure That Adds a Row and Returns a Scalar Object

ALTER PROCEDURE InsertOrder
     (
@CustomerID Nchar(5), @EmployeeID int, @OrderDate datetime, @RequiredDate datetime, 
@ShippedDate datetime, @ShipVia int, @Freight money, @ShipName nvarchar(40), 
@ShipAddress nvarchar(60), @ShipCity nvarchar(15),  @ShipRegion nvarchar(15), 
@ShipPostalCode nvarchar(10), @ShipCountry nvarchar(15)      )
AS
INSERT INTO Orders
               (CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, 
               Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, 
               ShipCountry)
VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, 
              @ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity, 
              @ShipRegion, @ShipPostalCode, @ShipCountry)
SELECT SCOPE_IDENTITY()                                           /*  To return the new OrderID */
     RETURN