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
LVL 4
Randy BristowSenior Systems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't use IF statement in a CTE but for your specific case you can easily transform it in a filter to be used in the WHERE clause:
	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 <= 1
			OR (@ComponentCodeID > 1 AND ComponentCodeID = @ComponentCodeID)
	ORDER BY S.ComponentCodeID, S.AssemblyID
END

Open in new window

1
Pawan KumarDatabase ExpertCommented:
Yes we cannot use IF statement like that.
 
The above code has TWO SYNTAX ERRORS so it will not compile and execute.

1. At the END Keyword is not required.  
2. There is a bracket missing in the OR condition.

Please use this.

;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 <= 1 OR (@ComponentCodeID > 1 AND ComponentCodeID = @ComponentCodeID))
ORDER BY S.ComponentCodeID, S.AssemblyID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Randy BristowSenior Systems AnalystAuthor Commented:
Thank you for your help. It works as expected.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.