t-sql UDF syntax error help!

[Dave]

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.s upport"
SET @compheir="comp ."
SET @socheir = "rec."
RETURN ((LEFT(@groupna me, 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.s upport"
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

[Steve Jorgensen]

Re: t-sql UDF syntax error help!

Unless you left something out here, you've left off the END statements, I
think.

On 11 Aug 2004 04:11:39 -0700, dhousman@gmail. com (Dave) wrote:
[color=blue]
>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.s upport"
>SET @compheir="comp ."
>SET @socheir = "rec."
>RETURN ((LEFT(@groupna me, 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.s upport"
>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[/color]

[MGFoster]

Re: t-sql UDF syntax error help!

-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQRp/7oechKqOuFEgEQJ RoACgi3/C0Yb1+gXZum/jNSTcxSohDaAAn0 Fl
FyVfkmwO+g04JSz s85avyFd6
=WkTK
-----END PGP SIGNATURE-----


Dave wrote:
[color=blue]
> 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.s upport"
> SET @compheir="comp ."
> SET @socheir = "rec."
> RETURN ((LEFT(@groupna me, 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.s upport"
> 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.[/color]

[Dave Housman]

Re: t-sql UDF syntax error help!



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!

[Dave Housman]

Re: t-sql UDF syntax error help!

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!