Link to home
Start Free TrialLog in
Avatar of Member_2_25505
Member_2_25505

asked on

Select in SQL Stored Procedure

I have a stored procedure that work for me but I want to add something to it and can't get it going. Here is (most of) the store procedure:

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

    -- Insert statements for procedure here
	WITH [Cost] AS 
	(
		SELECT QA.QuoteID, QA.AssemblyID, QA.AssemblyQuantity, QA.AssemblyName, QA.Multiplier, QA.ComponentCodeID, QD.ItemQuantity, QD.Burden, QD.PartNumber, QD.ItemDescription, 
			(CASE WHEN ItemCost > VendorContractPrice 
				THEN ItemCost 
				ELSE VendorContractPrice 
			  END) PartCost,

			(CASE WHEN ItemCost > VendorContractPrice 
				THEN ItemCost 
				ELSE VendorContractPrice 
			  END) *ItemQuantity * Burden ExtendedPartCost

		FROM tbl_QuoteAssemblyDetails QD
			RIGHT JOIN tbl_QuoteAssemblies QA ON QD.AssemblyID = QA.AssemblyID

		GROUP By QA.QuoteID, QA.AssemblyID, QA.AssemblyQuantity, QA.AssemblyName, QA.Multiplier, QA.ComponentCodeID, ItemCost, VendorContractPrice, ItemQuantity, Burden, PurchasedPart, ItemQuantity, PartNumber, ItemDescription
	),

	[Labor] As
	(
		SELECT		QL.AssemblyID,
					SUM(QL.LaborHours) AS [LaborHours]
		FROM		tbl_QuoteAssemblyLabor QL
		GROUP BY	QL.AssemblyID
	),

	[Summary] AS
	(
		SELECT C.QuoteID, C.AssemblyID, AssemblyQuantity, AssemblyName, Multiplier, ComponentCodeID, ItemQuantity, PartNumber, PartCost, ExtendedPartCost, Burden, ItemDescription
		FROM Cost C
		GROUP BY C.QuoteID, C.AssemblyID, AssemblyQuantity, AssemblyName, Multiplier, ComponentCodeID, ExtendedPartCost, PartNumber, ItemQuantity, PartCost, ExtendedPartCost, Burden, ItemDescription
	)

	SELECT S.QuoteID, S.AssemblyID, S.AssemblyQuantity, S.AssemblyName, S.Multiplier, S.ComponentCodeID, L.LaborHours, 
			PartNumber, ItemQuantity, PartCost, ExtendedPartCost, Burden, ItemDescription, Q.QuoteNumber, Q.Revision
	FROM Summary S 
		LEFT JOIN Labor L on L.AssemblyID = S.AssemblyID
		LEFT JOIN tbl_QuoteDetails Q ON S.QuoteID = Q.QuoteID
	WHERE ItemQuantity <> 0 AND Q.QuoteID = @QuoteID AND ComponentCodeID = @ComponentCodeID
	ORDER BY S.ComponentCodeID, S.AssemblyID

END

Open in new window



What I would like to do is put the SELECT at the end in an if statement but it is giving me an error. This is what it would look like if it worked:

	WITH [Cost] AS 
	(
		SELECT QA.QuoteID, QA.AssemblyID, QA.AssemblyQuantity, QA.AssemblyName, QA.Multiplier, QA.ComponentCodeID, QD.ItemQuantity, QD.Burden, QD.PartNumber, QD.ItemDescription, 
			(CASE WHEN ItemCost > VendorContractPrice 
				THEN ItemCost 
				ELSE VendorContractPrice 
			  END) PartCost,

			(CASE WHEN ItemCost > VendorContractPrice 
				THEN ItemCost 
				ELSE VendorContractPrice 
			  END) *ItemQuantity * Burden ExtendedPartCost

		FROM tbl_QuoteAssemblyDetails QD
			RIGHT JOIN tbl_QuoteAssemblies QA ON QD.AssemblyID = QA.AssemblyID

		GROUP By QA.QuoteID, QA.AssemblyID, QA.AssemblyQuantity, QA.AssemblyName, QA.Multiplier, QA.ComponentCodeID, ItemCost, VendorContractPrice, ItemQuantity, Burden, PurchasedPart, ItemQuantity, PartNumber, ItemDescription
	),

	[Labor] As
	(
		SELECT		QL.AssemblyID,
					SUM(QL.LaborHours) AS [LaborHours]
		FROM		tbl_QuoteAssemblyLabor QL
		GROUP BY	QL.AssemblyID
	),

	[Summary] AS
	(
		SELECT C.QuoteID, C.AssemblyID, AssemblyQuantity, AssemblyName, Multiplier, ComponentCodeID, ItemQuantity, PartNumber, PartCost, ExtendedPartCost, Burden, ItemDescription
		FROM Cost C
		GROUP BY C.QuoteID, C.AssemblyID, AssemblyQuantity, AssemblyName, Multiplier, ComponentCodeID, ExtendedPartCost, PartNumber, ItemQuantity, PartCost, ExtendedPartCost, Burden, ItemDescription
	)

	IF @ComponentCodeID > 1
		SELECT S.QuoteID, S.AssemblyID, S.AssemblyQuantity, S.AssemblyName, S.Multiplier, S.ComponentCodeID, L.LaborHours, 
				PartNumber, ItemQuantity, PartCost, ExtendedPartCost, Burden, ItemDescription, Q.QuoteNumber, Q.Revision
		FROM Summary S 
			LEFT JOIN Labor L on L.AssemblyID = S.AssemblyID
			LEFT JOIN tbl_QuoteDetails Q ON S.QuoteID = Q.QuoteID
		WHERE ItemQuantity <> 0 AND Q.QuoteID = @QuoteID AND ComponentCodeID = @ComponentCodeID
		ORDER BY S.ComponentCodeID, S.AssemblyID

	ELSE
		SELECT S.QuoteID, S.AssemblyID, S.AssemblyQuantity, S.AssemblyName, S.Multiplier, S.ComponentCodeID, L.LaborHours, 
				PartNumber, ItemQuantity, PartCost, ExtendedPartCost, Burden, ItemDescription, Q.QuoteNumber, Q.Revision
		FROM Summary S 
			LEFT JOIN Labor L on L.AssemblyID = S.AssemblyID
			LEFT JOIN tbl_QuoteDetails Q ON S.QuoteID = Q.QuoteID
		WHERE ItemQuantity <> 0 AND Q.QuoteID = @QuoteID
		ORDER BY S.ComponentCodeID, S.AssemblyID

END

Open in new window


Any help would be appreciated.

Thanks,
Randy
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Member_2_25505
Member_2_25505

ASKER

Thank you for your help. It works as expected.