Procedure: [AdventureWorks2016CTP3].[dbo].[uspGetOrderTrackingByTrackingNumber]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Type   P 
 Encrypted   
 ID   2045250341 
 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
@CarrierTrackingNumber   nvarchar 25 Input  

Collapse image Recordsets returned


Name  Datatype  Max length 
SalesOrderID int 4
CarrierTrackingNumber nvarchar 25
OrderTrackingID int 4
TrackingEventID int 4
EventName nvarchar 255
EventDetails nvarchar 2000
EventDateTime datetime2 8

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


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

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspGetOrderTrackingByTrackingNumber]
   @CarrierTrackingNumber [nvarchar](25) NULL
AS
BEGIN
/* Example:
      EXEC dbo.uspGetOrderTrackingByTrackingNumber 'EE33-45E8-9F'
      EXEC dbo.uspAddOrderTrackingEvent 53498, 7, 'invalid address, package is undeleverable'
      EXEC dbo.uspGetOrderTrackingByTrackingNumber 'EE33-45E8-9F'
*/
   SET NOCOUNT ON;

   IF (@CarrierTrackingNumber IS NULL)
   BEGIN
      RETURN;
   END;

   SELECT
      ot.SalesOrderID,
      ot.CarrierTrackingNumber,
      ot.OrderTrackingID,
      ot.TrackingEventID,
      te.EventName,
      ot.EventDetails,
      ot.EventDateTime
   FROM 
      Sales.OrderTracking ot, 
      Sales.TrackingEvent te
   WHERE
      ot.CarrierTrackingNumber = @CarrierTrackingNumber AND
      ot.TrackingEventID = te.TrackingEventID
   ORDER BY
      ot.SalesOrderID,
      ot.TrackingEventID;
END;

GO

Collapse image See also


List of procedures