I am trying to add a simple case statement to a stored procedure or
user defined function. However when I try and save the
function/procedure I get 2 syntax errors. Running the query in query
analyser works fine and a result is given with no syntax errors. I
believe its something to do with the spaces in the field names. Not my
choice as its an existing system I have to work around. Any help
greatly appreciated
SQL Query
DECLARE @pfid VARCHAR(100)
SET @pfid = '000101'
SELECT
Case
WHEN GetDate()
BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_end
THEN
((((gg_shop_product.Sale_Price/100)/
1.175)-("dbo"."Navision_Cost_Prices"."Unit Cost" *
Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/
1.175)) * 100
WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0
Then '100'
WHEN gg_shop_product.list_price > 0 THEN
((((gg_shop_product.List_Price /
100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit
Cost"*dbo.Navision_Codes."Navision
QTY"))/((gg_shop_product.List_Price/100 )/ 1.175 )) * 100
END as 'Margin'
from gg_shop_product INNER JOIN
gg_shop_variant ON gg_shop_product.pf_id =
gg_shop_variant.pf_id LEFT OUTER JOIN
gg_shop_cost_prices ON gg_shop_product.pf_id =
gg_shop_cost_prices.pf_id INNER JOIN
Navision_Codes ON gg_shop_variant.sku = Navision_Codes.sku
INNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =
Navision_Cost_Prices.No
WHERE gg_shop_product.pf_id = @pfid
User Defined Function (Errors Line 11 & 15)
CREATE FUNCTION dbo.get_Margin
(@pfid VARCHAR(100), @dtNow DATETIME)
RETURNS DECIMAL AS
BEGIN
DECLARE @Return as DECIMAL
SET @Return = (SELECT
Case
WHEN @dtNow
BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_end
THEN
((((gg_shop_product.Sale_Price/100)/
1.175)-(dbo.Navision_Cost_Prices."Unit Cost" *
Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/
1.175)) * 100
WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0
Then '100'
WHEN gg_shop_product.list_price > 0 THEN
((((gg_shop_product.List_Price /
100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit
Cost"*dbo.Navision_Codes."Navision
QTY"))/((gg_shop_product.List_Price/100 )/ 1.175 )) * 100
END as 'Margin'
from gg_shop_product INNER JOIN
gg_shop_variant ON gg_shop_product.pf_id =
gg_shop_variant.pf_id LEFT OUTER JOIN
gg_shop_cost_prices ON gg_shop_product.pf_id =
gg_shop_cost_prices.pf_id INNER JOIN
Navision_Codes ON gg_shop_variant.sku = Navision_Codes.sku
INNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =
Navision_Cost_Prices.No
WHERE gg_shop_product.pf_id = @pfid)
RETURN @Return
END