insert into table
(
    col 1
  , col 2
  , col 3
)

DECLARE @ var1 varchar(50)
DECLARE @ var2 int

SELECT.... blah blah

ALTER PROCEDURE [dbo].[StoredProc_InsertToTable]
	-- Add the parameters for the stored procedure here
	  @Mode int
	, @Search varchar(30)  
	, @fcOrderID char(9) = ''
	, @Carrier varchar(24) = ''
	, @PROTrackingNo char(30) = ''
	, @LoadNo varchar(24) = ''
	, @CarrierInvNo varchar(24) = ''
	, @dateInvoice datetime = ''
	, @InvAmnt float = ''
AS

/*
EXEC StoredProc_InsertToTable 1, ''




*/



/***************************************************************************************

	Mode 1 - Search by Fascor Order ID

***************************************************************************************/



IF @Mode = 1 --Search by Fascor Order ID
BEGIN
	IF EXISTS 
		(SELECT * 
		FROM Table2 
		WHERE LoadNo = 
			(SELECT DISTINCT LoadNo FROM Table2 
			  WHERE fcOrderID = @Search))
SELECT 
	   [uid]
	  ,[fcOrderID]
	  ,[p21OrderNo]
	  ,[Carrier]
	  ,[dateShipped]
	  ,[PROTrackingNo]
	  ,[LoadNo]
	  ,[CarrierInvNo]
	  ,[dateInvoice]
	  ,[InvAmnt]
	  ,[InvTTL]
	  ,[Delete]
FROM
(
SELECT 
	   [uid]
	  ,[fcOrderID]
	  ,[p21OrderNo]
	  ,[Carrier]
	  ,[dateShipped]
	  ,[PROTrackingNo]
	  ,[LoadNo]
	  ,[CarrierInvNo]
	  ,[dateInvoice]
	  ,[InvAmnt]
	  ,[InvTTL]
	  ,[Delete]
	  ,CASE 
	  	WHEN [fcOrderID] = @Search 
			THEN 1 
			ELSE 0 
	END AS [OrdVal]
  FROM dbo.Table2
  WHERE LoadNo COLLATE Latin1_General_CI_AS  = (SELECT c.Load_Nbr FROM Table1..delivery_arc c WHERE bol_id = @Search)
) T
ORDER BY [OrdVal] DESC, fcOrderId

	ELSE
		INSERT INTO Table2
			(	 
				  fcOrderID
				, p21OrderNo
				, Carrier
				, dateShipped
				, PROTrackingNo
				, LoadNo
			)


/***************   NEW SECTION SHOULD GO HERE FOR THE INSERT!   *************************************************/

DECLARE @OrderID varchar(25)
DECLARE @DelivNo varchar(25)

DECLARE @NumberOnInvoice varchar(25)

SELECT 
	  @OrderID = Order_ID
	, @DelivNo = Delivery_Nbr	
FROM Table1.[dbo].[DeliveryOrders_Arc]
WHERE Order_ID = @NumberOnInvoice

IF @OrderID IS NULL
BEGIN
	print 'This Came From The IF Section'
	
	/************************************************
	Get the Delivery, Load, and Pro Numbers and put
	them into variables to use in later SELCET
	************************************************/
	
	DECLARE @DeliveryNo varchar(25)
	DECLARE @LoadNo1 varchar(25)
	DECLARE @ProNbr varchar(25)
	
	SELECT 
		  @LoadNo1 = A.Load_Nbr 
		, @ProNbr = A.Pro_Nbr 
		, @DeliveryNo = A.Delivery_Nbr 
	FROM Table1..delivery_arc A
	WHERE A.BOL_ID = @NumberOnInvoice  --'0055404872'
	
	
	
	
	/************************************************
	Get the TRU or CORRECT OrderID By Using The 
	Delivery No Obtained in previous query and Store
	it in @TrueOrderID Variable
	************************************************/
	
	DECLARE @TrueOrderID varchar(25)
	SET @TrueOrderID =
	(
	SELECT
		B.Order_ID
	FROM Table1.[dbo].[DeliveryOrders_Arc] B
	WHERE B.Delivery_Nbr = @DeliveryNo
	)
	
	
	
	/************************************************
	Compile everything together in this one SELECT
	by using the Tru/Correct OrderNo, along with the
	LoadNo and ProNo Variables From Frist SELECT
	************************************************/
	
		SELECT DISTINCT
		A.shipment_number AS FCOrderID
		, X.source_order_no AS p21OrderNo
		, A.carrier_name AS Carrier
		, X.date_last_Modified AS ShipDate
		, @LoadNo1 AS LoadNo
		, DA.Pro_Nbr --@ProNbr2 AS Pro_Nbr
	FROM Table3.bryanequipmentdotcom.dbo.BES_PackageTrackingDetails A
		JOIN Table3.Commerce_Center.dbo.p21_view_pick_list_hdr X 
			ON A.shipment_number = X.pick_list_no
		JOIN Table1.dbo.DeliveryOrders_Arc DOA
			ON A.shipment_number = DOA.Order_ID COLLATE Latin1_General_CI_AS
		JOIN Table1.dbo.Delivery_Arc DA
			ON DOA.Delivery_Nbr = DA.Delivery_Nbr COLLATE Latin1_General_CI_AS
	WHERE A.Shipment_number IN 
							(SELECT 
								A.Order_ID COLLATE Latin1_General_CI_AS
							 FROM Table1.[dbo].[DeliveryOrders_Arc] A
								JOIN Table1.[dbo].[Delivery_Arc] B
								ON A.Delivery_Nbr = B.Delivery_Nbr 
							 WHERE B.Load_Nbr = @LoadNo1 
							 AND BOL_Date > '2011-01-01' 
							)

END



/*
ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, 
ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, 
ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!, ELSE!!
*/


ELSE
BEGIN
	print 'This Came From The ELSE Section'
	
	/************************************************
	Get the Delivery, Load, and Pro Numbers and put
	them into variables to use in later SELCET
	************************************************/
	DECLARE @DeliveryNo2 varchar(25)
	DECLARE @LoadNo2 varchar(25)
	DECLARE @ProNbr2 varchar(25)
	
	SELECT 
		  @LoadNo2 = A.Load_Nbr 
		, @ProNbr2 = A.Pro_Nbr 
		, @DeliveryNo2 = A.Delivery_Nbr 
	FROM Table1..delivery_arc A
	WHERE Delivery_Nbr = 
	(
	SELECT
		A.Delivery_Nbr
	FROM Table1.[dbo].[DeliveryOrders_Arc] A
	WHERE order_id = @NumberOnInvoice
	)

	
	
	/************************************************
	Get the TRU or CORRECT OrderID By Using The 
	Delivery No Obtained in previous query and Store
	it in @TrueOrderID Variable
	************************************************/
	
	DECLARE @TrueOrderID2 varchar(25)
	SET @TrueOrderID2 =
	(
	SELECT
		B.Order_ID
	FROM Table1.[dbo].[DeliveryOrders_Arc] B
	WHERE B.Delivery_Nbr = @DeliveryNo2
	)
	
	
	
	/************************************************
	Compile everything together in this one SELECT
	by using the Tru/Correct OrderNo, along with the
	LoadNo and ProNo Variables From Frist SELECT
	************************************************/
	
	SELECT DISTINCT
		A.shipment_number AS FCOrderID
		, X.source_order_no AS p21OrderNo
		, A.carrier_name AS Carrier
		, X.date_last_Modified AS ShipDate
		, @LoadNo2 AS LoadNo
		, DA.Pro_Nbr 
	FROM Table3.bryanequipmentdotcom.dbo.BES_PackageTrackingDetails A
		JOIN Table3.Commerce_Center.dbo.p21_view_pick_list_hdr X 
			ON A.shipment_number = X.pick_list_no
		JOIN Table1.dbo.DeliveryOrders_Arc DOA
			ON A.shipment_number = DOA.Order_ID COLLATE Latin1_General_CI_AS
		JOIN Table1.dbo.Delivery_Arc DA
			ON DOA.Delivery_Nbr = DA.Delivery_Nbr COLLATE Latin1_General_CI_AS
	WHERE A.Shipment_number IN 
							(SELECT 
								A.Order_ID COLLATE Latin1_General_CI_AS
							 FROM Table1.[dbo].[DeliveryOrders_Arc] A
								JOIN Table1.[dbo].[Delivery_Arc] B
								ON A.Delivery_Nbr = B.Delivery_Nbr 
							 WHERE B.Load_Nbr = @LoadNo2 
							 AND BOL_Date > '2011-01-01' 
							)
	END
END