I keep getting an error message "incorrect syntax near keyword case"
when trying to run this:

USE DEDUPE
GO

CREATE FUNCTION fnCleanString
(
@mString varchar (255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE
@mChar char(1),
@msTemp varchar(255),
@miLen int,
@i int,
@iAsc int
BEGIN
set @mChar = ''
set @msTemp = ''
set @miLen = Len(@mString)
set @i = 1
while @i <= @miLen
begin
set @mChar = substring(@mString,@i,1)
set @iAsc = Ascii(@mChar)
case
when @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mChar
when iAsc >= 65 And iAsc <= 90 Then set @mChar = @mChar
when iAsc >= 49 And iAsc <= 57 Then set @mChar = @mChar
else @mChar = ""
end
set @msTemp = @msTemp & @mChar
set @i = @i + 1
end
END
RETURN @msTemp
END

Can anybody point out what I'm doing wrong?
Thanks.
Randy

EoRaptor013 (rchrismon@patmedia.net) writes:
> I keep getting an error message "incorrect syntax near keyword case"
> when trying to run this:
>...
> case
> when @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mChar
> when iAsc >= 65 And iAsc <= 90 Then set @mChar = @mChar
> when iAsc >= 49 And iAsc <= 57 Then set @mChar = @mChar
> else @mChar = ""
> end
> Can anybody point out what I'm doing wrong?

A general advice when you have problem with syntax, look it up in
Books Online, instead of just making wild guesses. Books Online
says about CASE: "Evaluates a list of conditions and returns one of multiple
possible result expressions. ". A little further it says: "The simple CASE
function compares ...". If that doesn't ring a bell, you will get some
guidance from the Examples section.

CASE is an expression. Not a statement.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"EoRaptor013" <rchrismon@patmedia.net> wrote in message
news:1108159891.406126.35640@c13g2000cwb.googlegro ups.com...
>I keep getting an error message "incorrect syntax near keyword case"
> when trying to run this:
>
> USE DEDUPE
> GO
>
> CREATE FUNCTION fnCleanString
> (
> @mString varchar (255)
> )
> RETURNS varchar(255)
> AS
> BEGIN
> DECLARE
> @mChar char(1),
> @msTemp varchar(255),
> @miLen int,
> @i int,
> @iAsc int
> BEGIN
> set @mChar = ''
> set @msTemp = ''
> set @miLen = Len(@mString)
> set @i = 1
> while @i <= @miLen
> begin
> set @mChar = substring(@mString,@i,1)
> set @iAsc = Ascii(@mChar)
> case
> when @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mChar
> when iAsc >= 65 And iAsc <= 90 Then set @mChar = @mChar
> when iAsc >= 49 And iAsc <= 57 Then set @mChar = @mChar
> else @mChar = ""
> end
> set @msTemp = @msTemp & @mChar
> set @i = @i + 1
> end
> END
> RETURN @msTemp
> END
>
> Can anybody point out what I'm doing wrong?
> Thanks.
> Randy
>

CASE is an expression, not a control of flow statement - see the examples in
Books Online. Or you can use a single IF (and I think you mean 97, not 87,
otherwise your first two ranges would overlap):

if not (
@iAsc between 97 and 122 or
@iAsc between 65 and 90 or
@iAsc between 49 and 57
)
set mChar = ''

Simon

Thank you, responders.
Please believe, I always read the BOL before asking. In this case (no
pun intended), I believe the BOL could be clearer. In light of your
responses, I looked again and believe the real clue I should have
gotten was the use of the word "function". A function returns a value,
therefore I need something to hold that value. I agree that the
examples should help. Unfortunately, my version of the BOL does not
show CASE being used to assign a value to a variable or other
receptacle. Since I'm not the sharpest pencil in the box, the SELECT
statements didn't turn on any light blubs for me.
Again, thank you all very much.
Randy