Procedure: [AdventureWorks2016CTP3].[dbo].[uspAddOrderTrackingEvent]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Type   P 
 Encrypted   
 ID   2029250284 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description Datatype  Max length  Type  ReadOnly
@SalesOrderID   int 4 Input  
@TrackingEventID   int 4 Input  
@EventDetails   nvarchar 2000 Input  

Collapse image Objects that [dbo].[uspAddOrderTrackingEvent] depends on


Object name Object type Dep level
[dbo].[uspLogError] Procedure 1
[Sales].[OrderTracking] Table 1
Total 2 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

----------------------------------------------------------------
-- Create supporting stored procedures
----------------------------------------------------------------
CREATE PROCEDURE [dbo].[uspAddOrderTrackingEvent]
   @SalesOrderID INT,
   @TrackingEventID INT,
   @EventDetails NVARCHAR(2000)
AS
BEGIN
/* Example:
      exec dbo.uspGetOrderTrackingBySalesOrderID 53498
      exec dbo.uspAddOrderTrackingEvent 53498, 7, 'invalid address, package is undeleverable'
      exec dbo.uspGetOrderTrackingBySalesOrderID 53498
*/
   SET NOCOUNT ON;

   BEGIN TRY
      BEGIN TRANSACTION;

      DECLARE @TrackingNumber NVARCHAR(25);

      SET @TrackingNumber = (
         SELECT TOP 1 ot.CarrierTrackingNumber 
           FROM Sales.OrderTracking ot
          WHERE ot.SalesOrderID = @SalesOrderID);

      IF (@TrackingNumber IS NULL)
      BEGIN
         SET @TrackingNumber = SUBSTRING(CONVERT(CHAR(255), NEWID()),2,25);
      END;

      INSERT INTO Sales.OrderTracking
         (SalesOrderID, CarrierTrackingNumber, TrackingEventID, EventDetails, EventDateTime)
      VALUES
         (@SalesOrderID, @TrackingNumber, @TrackingEventID, @EventDetails, GETDATE());

      COMMIT TRANSACTION;
   END TRY
   BEGIN CATCH
      -- Rollback any active or uncommittable transactions before
      -- inserting information in the ErrorLog
      IF @@TRANCOUNT > 0
      BEGIN
         ROLLBACK TRANSACTION;
      END

      EXECUTE [dbo].[uspLogError];
   END CATCH;
END;
GO

Collapse image See also


List of procedures