473,378 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Strange Problew with user defined function or stored procedure

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
Jul 23 '05 #1
3 2518
Ray
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" <Ga*******@gmail.com> wrote in message
news:1b**************************@posting.google.c om...
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

Jul 23 '05 #2
Thanks, I changed the quotes to brackets and used brackets on every
table and field name. All seems to be ok now

Jul 23 '05 #3
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)
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: JHB | last post by:
Hi. I'm really new to MSSQL, so therefore my question can sound stupid. Is it possible to use a function written in a module in MS-ACCESS in a stored procedure? Or how can it be done, it is a...
2
by: Steve D | last post by:
I've looked all over but can't find a solid answer. I've got a function that runs from a View and when the function runs the first time it is calculating a Temperature for a group of Formulas. ...
13
by: dawatson833 | last post by:
I have several stored procedures with parameters that are defined with user defined data types. The time it takes to run the procedures can take 10 - 50 seconds depending on the procedure. If I...
4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
5
by: Lili | last post by:
I'm having problems creating a simple stored procedure in DB2. Can someone help? Here is the screen dump when I tried to load the stored procedure. Thanks for any help. Create procedure...
3
by: Bilbo | last post by:
I have a a headscratcher here: I have a form that when submitted should do 2 things when a user enters data and then clicks the Add button. Here goes: 1. Call a stored procedure called...
10
by: John Bailo | last post by:
I created a stored procedure called MANLIB.MAN535CL based on a CL/COBOL program. I know it works because I can run it successfully. However, I don't really understand where it is on the...
0
by: mongolian | last post by:
Hello, I am trying to execute an Extended Stored Procedure in a User Defined Function I wrote. It really is quite simple but I get an error saying "Only functions and extended stored procedures...
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.