473,320 Members | 1,914 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,320 software developers and data experts.

t-sql UDF syntax error help!

Hi,

I'm running Access '03, SQL SERVER 2k on Windows XP SP1. I've not got
a ton of experience in writing UDF's for t-sql.

In SQL Query Analyzer, I'm trying to write a user defined scalar
function that returns a bit. I'm getting a vague error- so I'm not
quite sure what's wrong. I want the function to return true if the
parameter varchar matches one of three varchar's.

The error i'm getting is "[Microsoft][ODBC SQL Server Driver]Syntax
error or access violation". Other than that I'm

Code follows:

CREATE FUNCTION Kraut_Full.dbo.usefulgrouptest (@groupname AS
VARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE @supheir varchar(20)
DECLARE @compheir varchar(20)
DECLARE @socheir varchar (20)
SET @supheir="alt.support"
SET @compheir="comp."
SET @socheir = "rec."
RETURN ((LEFT(@groupname, LEN(@supheir))=@supheir) OR (LEFT(@tmpstr,
LEN(@compheir))=@compheir) OR (Left(@tmpstr, LEN(@socheir))=@socheir))

--I've also tried this:

CREATE FUNCTION Kraut_Full.dbo.usefulgrouptest (@groupname AS
VARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE @supheir varchar(20)
DECLARE @compheir varchar(20)
DECLARE @socheir varchar (20)
DECLARE @tmpstr varchar (255)
SET @supheir="alt.support"
SET @compheir="comp."
SET @socheir = "rec."

IF (LEFT(@tmpstr, LEN(@supheir))=@supheir){
return TRUE}
else if (LEFT(@tmpstr, LEN(@compheir))=@compheir){
return TRUE}
else if (Left(@tmpstr, LEN(@socheir))=@socheir){
return TRUE}
else return false
END

So, I'd appreciate any help someone can give on this. I'm aware that
multiple declarations/assignments can be done at once- but right now
I'm just trying to get it to work.

Thanks in advance,
Dave
Nov 13 '05 #1
4 3868
Unless you left something out here, you've left off the END statements, I
think.

On 11 Aug 2004 04:11:39 -0700, dh******@gmail.com (Dave) wrote:
Hi,

I'm running Access '03, SQL SERVER 2k on Windows XP SP1. I've not got
a ton of experience in writing UDF's for t-sql.

In SQL Query Analyzer, I'm trying to write a user defined scalar
function that returns a bit. I'm getting a vague error- so I'm not
quite sure what's wrong. I want the function to return true if the
parameter varchar matches one of three varchar's.

The error i'm getting is "[Microsoft][ODBC SQL Server Driver]Syntax
error or access violation". Other than that I'm

Code follows:

CREATE FUNCTION Kraut_Full.dbo.usefulgrouptest (@groupname AS
VARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE @supheir varchar(20)
DECLARE @compheir varchar(20)
DECLARE @socheir varchar (20)
SET @supheir="alt.support"
SET @compheir="comp."
SET @socheir = "rec."
RETURN ((LEFT(@groupname, LEN(@supheir))=@supheir) OR (LEFT(@tmpstr,
LEN(@compheir))=@compheir) OR (Left(@tmpstr, LEN(@socheir))=@socheir))

--I've also tried this:

CREATE FUNCTION Kraut_Full.dbo.usefulgrouptest (@groupname AS
VARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE @supheir varchar(20)
DECLARE @compheir varchar(20)
DECLARE @socheir varchar (20)
DECLARE @tmpstr varchar (255)
SET @supheir="alt.support"
SET @compheir="comp."
SET @socheir = "rec."

IF (LEFT(@tmpstr, LEN(@supheir))=@supheir){
return TRUE}
else if (LEFT(@tmpstr, LEN(@compheir))=@compheir){
return TRUE}
else if (Left(@tmpstr, LEN(@socheir))=@socheir){
return TRUE}
else return false
END

So, I'd appreciate any help someone can give on this. I'm aware that
multiple declarations/assignments can be done at once- but right now
I'm just trying to get it to work.

Thanks in advance,
Dave


Nov 13 '05 #2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In your first example you havent' declared @tmpstr.

In both examples you haven't assigned a value to @tmpstr.

Instead of using TRUE/FALSE you may wish to try 1 and 0.

In your second example you don't need the curly brackets (those are used
in SQL BOL to show parameters - not meant to be included in the
statement). Also, you don't need all the extra parentheses. The
IF...ELSE statement can be re-written like this:

IF LEFT(@tmpstr, LEN(@supheir)) = @supheir RETURN 1
IF LEFT(@tmpstr, LEN(@compheir)) = @compheir RETURN 1
IF LEFT(@tmpstr, LEN(@socheir)) = @socheir RETURN 1
RETURN 0

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRp/7oechKqOuFEgEQJRoACgi3/C0Yb1+gXZum/jNSTcxSohDaAAn0Fl
FyVfkmwO+g04JSzs85avyFd6
=WkTK
-----END PGP SIGNATURE-----
Dave wrote:
Hi,

I'm running Access '03, SQL SERVER 2k on Windows XP SP1. I've not got
a ton of experience in writing UDF's for t-sql.

In SQL Query Analyzer, I'm trying to write a user defined scalar
function that returns a bit. I'm getting a vague error- so I'm not
quite sure what's wrong. I want the function to return true if the
parameter varchar matches one of three varchar's.

The error i'm getting is "[Microsoft][ODBC SQL Server Driver]Syntax
error or access violation". Other than that I'm

Code follows:

CREATE FUNCTION Kraut_Full.dbo.usefulgrouptest (@groupname AS
VARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE @supheir varchar(20)
DECLARE @compheir varchar(20)
DECLARE @socheir varchar (20)
SET @supheir="alt.support"
SET @compheir="comp."
SET @socheir = "rec."
RETURN ((LEFT(@groupname, LEN(@supheir))=@supheir) OR (LEFT(@tmpstr,
LEN(@compheir))=@compheir) OR (Left(@tmpstr, LEN(@socheir))=@socheir))

--I've also tried this:

CREATE FUNCTION Kraut_Full.dbo.usefulgrouptest (@groupname AS
VARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE @supheir varchar(20)
DECLARE @compheir varchar(20)
DECLARE @socheir varchar (20)
DECLARE @tmpstr varchar (255)
SET @supheir="alt.support"
SET @compheir="comp."
SET @socheir = "rec."

IF (LEFT(@tmpstr, LEN(@supheir))=@supheir){
return TRUE}
else if (LEFT(@tmpstr, LEN(@compheir))=@compheir){
return TRUE}
else if (Left(@tmpstr, LEN(@socheir))=@socheir){
return TRUE}
else return false
END

So, I'd appreciate any help someone can give on this. I'm aware that
multiple declarations/assignments can be done at once- but right now
I'm just trying to get it to work.


Nov 13 '05 #3


Hi,
I accidentally cut off the end statement in copying to the post.
Thanks, though!
-Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4
Hi,
In mixing and matching to come up with previous versions of the code, i
accidentally left out the declaration (it was there when I actually
tried to compile). But I tried using return 1 and return 0 instead of
True and false, and it worked. THANK YOU SO MUCH!
I find that really odd. Methinks i needs to read more documentation.
THANKS AGAIN!
-Dave
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

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

Similar topics

14
by: sam | last post by:
When I run this SQL query: SELECT u.*, o.* FROM users u, orders o WHERE TO_DAYS(o.order_date) BETWEEN TO_DAYS('2003-09-20')-10 AND TO_DAYS('2003-09-20')+10
2
by: Dr DOOM | last post by:
Hi folks...I've just installed asp.net framework and asp.net sdk and I've cut and pasted a file from the web to test that it works. The problem is that I get this error message which says: ...
2
by: Phil Powell | last post by:
I am not sure why this is producing a SQL Server related error, but w/o having an instance of SQL Server on my machine to verify anything further, can you all help me with this? <!--- validate()...
8
by: Bob | last post by:
I tried this in <input name="some_name" type="text" value="" onChange="auto_select(0);"> but it gives a syntax error. var cb = this.form.name alert( "name=" + cb )
1
by: MCWarrior76 | last post by:
got a question for anyone that can help......im writin a game program for my nephew, and came across a problem, I wrote, if((TR=='a')&&(dresser_pick=='b')) || ((TR=='a')&&(dresser_pick2=='b')) it...
2
by: coolindienc | last post by:
Today I am going nuts. This is another one that is not working. It keeps giving me syntax error at highlighted line. No matter what I do I still have that error at the same line. It seems that my...
1
mikeinspain
by: mikeinspain | last post by:
Hi Guys I am getting the following error: Parse error: syntax error, unexpected '}' in /home/9144/domains/cbweb.co.uk/html/propertyEnquiry.php on line 5 This is the referring PHP code... ...
5
by: Ty | last post by:
Syntax Error-Help!! My error "SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007" . I'm using yyyy-mm-dd. I can see the dates in a column on...
3
by: benicio | last post by:
The subject update failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 6' at line 5. THis...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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...

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.