By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

t-sql UDF syntax error help!

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
-----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

P: n/a


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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.