We help IT Professionals succeed at work.

SQL Insert using both select statements and parameters

sinjin
sinjin asked
on
Hi all,
I suspect the following sproc could be cleaned up a lot but I just don't see it right now.  My main area I wish to clean up is in the various select statements as each of them uses the same From... inner join... Where clauses.  
thx in advance.
CREATE PROCEDURE ps_ShipItems

		@CODTrackingNo nvarchar(50),
		@DateShipped smalldatetime,
		@InvoiceNo nvarchar(50),
		@ShippedBy nvarchar(50),
		@ShipQty int,
		@SODetailID int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


Insert Into SalesOrder_detailTracking 
(
	TrackingNumber, Datecode, MfgPN, MFGRName, RoHS, SupplierName, Comments, InternalNo,
	SalesOrderNumber, DateShipped, InvoiceNumber, ShipQty, CODTrackingNumber, ShippedBy, SalesOrderDetailID
)
VALUES
(
(select T.TrackingNumber from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID), 
(select T.Datecode from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID), 
(select T.MfgPN from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID),
(select T.Mfgr_Name from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID),
(select T.RoHS from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID),
(select T.SupplierName from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID),
(select T.Comments from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID),
(select T.InternalNo from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID),
(select d.SalesOrderNumber from PurchaseOrder_detailTracking T inner join PurchaseOrder_details D on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID inner join PurchaseOrder_header H on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID WHERE H.SalesOrderDetailID = @SODetailID),
 @DateShipped, @InvoiceNo, @ShipQty, @CODTrackingNo, @ShippedBy, @SODetailID)
END
GO

Open in new window

Comment
Watch Question

View Solution Only

Commented:
this should work
Insert Into SalesOrder_detailTracking 
(
	TrackingNumber, Datecode, MfgPN, MFGRName, RoHS, SupplierName, Comments, InternalNo,
	SalesOrderNumber, DateShipped, InvoiceNumber, ShipQty, CODTrackingNumber, ShippedBy, SalesOrderDetailID
)



select T.TrackingNumber ,T.Datecode,T.MfgPN ,T.Mfgr_Name ,T.RoHS ,T.SupplierName ,T.Comments ,T.InternalNo ,d.SalesOrderNumber ,
 @DateShipped, @InvoiceNo, @ShipQty, @CODTrackingNo, @ShippedBy, @SODetailID


from PurchaseOrder_detailTracking T 
inner join PurchaseOrder_details D 
on T.PurchaseOrderDetailID = D.PurchaseOrderDetailID 
inner join PurchaseOrder_header H 
on D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID
WHERE H.SalesOrderDetailID = @SODetailID

Open in new window

sinjin

Author

Commented:
I will try it now....
Anthony Perkins
Top Expert 2012

Commented:
Something like this:
CREATE PROCEDURE ps_ShipItems
    @CODTrackingNo nvarchar(50),
    @DateShipped smalldatetime,
    @InvoiceNo nvarchar(50),
    @ShippedBy nvarchar(50),
    @ShipQty int,
    @SODetailID int

AS 

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;

INSERT  SalesOrder_detailTracking (
        TrackingNumber,
        Datecode,
        MfgPN,
        MFGRName,
        RoHS,
        SupplierName,
        Comments,
        InternalNo,
        SalesOrderNumber,
        DateShipped,
        InvoiceNumber,
        ShipQty,
        CODTrackingNumber,
        ShippedBy,
        SalesOrderDetailID)
SELECT  T.TrackingNumber,
        T.Datecode,
        T.MfgPN,
        T.Mfgr_Name,
        T.RoHS,
        T.SupplierName,
        T.Comments,
        T.InternalNo,
        d.SalesOrderNumber,
        @DateShipped,
        @InvoiceNo,
        @ShipQty,
        @CODTrackingNo,
        @ShippedBy,
        @SODetailID
FROM    PurchaseOrder_detailTracking T
        INNER JOIN PurchaseOrder_details D ON T.PurchaseOrderDetailID = D.PurchaseOrderDetailID
        INNER JOIN PurchaseOrder_header H ON D.PurchaseOrderHeaderID = H.PurchaseOrderInfoID
WHERE   H.SalesOrderDetailID = @SODetailID

END
GO

Open in new window

sinjin

Author

Commented:
tyvm!  I knew it was going to be easy.