We help IT Professionals succeed at work.
Get Started

SQL Insert using both select statements and parameters

sinjin
sinjin asked
on
221 Views
Last Modified: 2012-05-09
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
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE