January 13, 2009 at 3:58 pm
ALTER PROC prodTool.CalculateTaxablePrice
--@productId int,
--@taxablePrice decimal = 0
AS
--IF(EXISTS(Select PackageID FROM ProductPackage WHERE PackageID =41806))
--BEGIN
DECLARE @sumPhysical int
DECLARE @sumAllProducts int
WITH ProductTaxable(ProductOption, DeliveryType, UnitPrice, ProductID)
AS
(SELECT ProductOption, DeliveryType, UnitPrice, p.ProductID
FROM Product p INNER JOIN
ProductPackage pp
ON p.ProductID=pp.ProductID
WHERE pp.PackageID=41806
AND pp.OprionProduct>=0
),
WITH PysicalProductsTable(UnitPricePhysical)
AS
(SELECT (CASE WHEN ProductOption=0 THEN SUM(UnitPrice)
WHEN ProductOption > 0 THEN MAX(UnitPrice) ELSE NULL END) AS UnitPricePhysical
FROM ProductTaxable WHERE DeliveryType=1
GROUP BY ProductOption
),
WITH AllProductsTable(UnitPriceAll)
AS
(SELECT (CASE WHEN ProductOption=0 THEN SUM(UnitPrice)
WHEN ProductOption > 0 THEN MAX(UnitPrice) ELSE NULL END) AS UnitPriceAll
FROM ProductTaxable
GROUP BY ProductOption
)
SELECT @sumPhysical=SUM(UnitPricePhysical)
FROM PysicalProductsTable;
SELECT @sumAllProducts=SUM(UnitPriceAll)
FROM AllProductsTable
--END
This procedure is giving following errors:
Msg 156, Level 15, State 1, Procedure CalculateTaxablePrice, Line 39
Incorrect syntax near the keyword 'WITH'.
Msg 156, Level 15, State 1, Procedure CalculateTaxablePrice, Line 53
Incorrect syntax near the keyword 'WITH'.
Help Appreciated.
January 13, 2009 at 4:24 pm
I'm pretty sure you only need 1 with statement.
Take out the others.
January 14, 2009 at 6:17 am
To create a common table expression (CTE) the last statment prior to the CTE must be closed off with a semi-colon, in your case:
DECLARE @sumAllProducts INT ;
And, you don't need to use the key word WITH over and over again. I cleaned up the query this way:
ALTER PROC prodTool.CalculateTaxablePrice
--@productId int,
--@taxablePrice decimal = 0
AS --IF(EXISTS(Select PackageID FROM ProductPackage WHERE PackageID =41806))
--BEGIN
DECLARE @sumPhysical INT
DECLARE @sumAllProducts INT ;
WITH ProductTaxable(ProductOption, DeliveryType, UnitPrice, ProductID)
AS (SELECT ProductOption
,DeliveryType
,UnitPrice
,p.ProductID
FROM Product p
INNER JOIN ProductPackage pp
ON p.ProductID = pp.ProductID
WHERE pp.PackageID = 41806
AND pp.OprionProduct >= 0
),
PysicalProductsTable(UnitPricePhysical)
AS (SELECT (CASE WHEN ProductOption = 0 THEN SUM(UnitPrice)
WHEN ProductOption > 0 THEN MAX(UnitPrice)
ELSE NULL
END) AS UnitPricePhysical
FROM ProductTaxable
WHERE DeliveryType = 1
GROUP BY ProductOption
),
AllProductsTable(UnitPriceAll)
AS (SELECT (CASE WHEN ProductOption = 0 THEN SUM(UnitPrice)
WHEN ProductOption > 0 THEN MAX(UnitPrice)
ELSE NULL
END) AS UnitPriceAll
FROM ProductTaxable
GROUP BY ProductOption
)
SELECT @sumPhysical = SUM(UnitPricePhysical)
FROM PysicalProductsTable ;
SELECT @sumAllProducts = SUM(UnitPriceAll)
FROM AllProductsTable
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 9:15 am
Thank you very much i appreciate your support.:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply