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:
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:
Any help would be appreciated.
Thanks,
Randy
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
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
Any help would be appreciated.
Thanks,
Randy
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER