Microsoft SQL Server forum - Strange Problew with user defined function or stored procedure

Author

Strange Problew with user defined function or stored procedure

Gary Besta

2005-04-22, 7:23 am

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

Ray

2005-04-22, 7:23 am

Try using brackets [] instead of double quotes or set SET QUOTED_IDENTIFIER
on before the create proc and off after it. Also be more consistent with
the usage of quotes or brakets.

"Gary Besta" <GaryBesta@gmail.com> wrote in message
news:1b69b9ad.0504220249.28053f9e@posting.google.com...
>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

GaryBesta@gmail.com

2005-04-22, 7:23 am

Thanks, I changed the quotes to brackets and used brackets on every
table and field name. All seems to be ok now

Hugo Kornelis

2005-04-22, 8:23 pm

On 22 Apr 2005 03:49:59 -0700, Gary Besta wrote:

>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.
(snip)

Hi Gary,

No, it's not the spaces, but the double quotation mark character (").

If your table names or column names contain characters that are not
allowed, or if they are reserved words, you must delimit them. The ANSI
standard specifies that double quotation marks have to be used for this
purpose. Unfortunately,
a) Older versions of SQL Server used the double quotation mark as string
delimiter;
b) Almost all current Microsoft products, including Access, still use
the doouble quotation mark as string delimiter;
c) Both Access and SQL Server use square brackets as delimiter for table
and column names;
d) Support for ANSI standard treatment in SQL Server 2000 is possible,
as a configurable option - and not all tools that are installed with SQL
Server use the same defaults.

I've read that you've already changed the double quotation marks to
square brackets - and yes, that will work perfectly. But you should be
aware that the square brackets are "Microsoft only", whereas the double
quotation marks are ANSI standard and should be recognized by all major
databases. If you expect that you might have to port your code somewhere
in the future, stick to using double quotation marks and avoid the
square brackets like the plague (unless you expect to be porting to
Access, of course <grin> ).

And regardless of your choice here, make sure that all the tools you use
will treat double quotation marks as delimiters for table and column
names (aka identifiers), not as delimiters for string literals.

In Query Analyzer, you can control the settings for individual
connections with the 'Query' / 'Current Connection Properties' menu, or
you can control the default settings (used for new connections) with the
'Tools' / 'Options' menu, tab 'Connection Properties'. In both cases,
you'll have to make sure that the option 'Set quoted_identifier' is
checked to make sure that double quotation marks are treated as
specified in the ANSI standard (you'll still be able to use square
brackets to delimit table and column names, though). Or uncheck the
option if you are running legacy code that still uses double quotation
marks to delimit string literals.
IIRC, the default setting for 'Set quoted_identifier' is ON in Query
Analyzer; that's why running the code in EM works.

In Enterprise Manager, you can control the settings with the menu option
'Tools' / 'SQL Server Configuration Properties', tab 'Connections'. In
the attribute list, scroll down to 'Quoted identifier' and check this
option to force conformance to ANSI standard.

If you (for whatever reason) don't want to change the default option,
you can still change them. Just insert the line
SET QUOTED_IDENTIFIER ON
before the CREATE PROC (or CREATE TRIGGER, CREATE FUNCTION or whatever)
line.

Finally, are you aware that you don't need Enterprise Manager to create
functions (or other database objects)? You can just run the CREATE
FUNCTION statement in Query Analyzer to create it. It would have saved
you this hassle :-) plus, there are some other limitations that
Enterprise Manager imposes.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links