Problem with Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with Function

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-11-09 : 14:54:09

I am getting an error when trying to create this function...what is wrong?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Function VarcharToNumeric4
(@str VARCHAR(400))
RETURNS NUMERIC(14,4) AS
BEGIN
Declare @ConvertedNum as Numeric(14,4)
IF (isnumeric(@str) )
Begin
SET @ConvertedNum = Cast(@str as numeric(14,4))
ELSE
SET @ConvertedNum = 0
End
RETURN @ConvertedNum
END

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-11-09 : 14:56:48

Sorry...this is my error msg:
Msg 4145, Level 15, State 1, Procedure VarcharToNumeric4, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'Begin'.
Msg 156, Level 15, State 1, Procedure VarcharToNumeric4, Line 10
Incorrect syntax near the keyword 'ELSE'.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-09 : 15:01:06

You realize that ISNUMERIC is not a very reliable/good function to be using?

There are a couple things wrong I fixed them and made a couple of tweaks:

Create Function VarcharToNumeric4 
(
    @str VARCHAR(400)
)
RETURNS NUMERIC(14,4) 
AS 
BEGIN
    DECLARE @ConvertedNum Numeric(14,4) = 0;
    
    IF (ISNUMERIC(@str) = 1)
    BEGIN
        SET @ConvertedNum = CAST(@str AS NUMERIC(14,4)) 
    END

    RETURN @ConvertedNum 
END

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-11-09 : 15:08:18

Looks like i just needed
IF ISNUMERIC(LTRIM(RTRIM(@str))) = 1

works now

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-11-09 : 15:09:46

Lamprey...yours looks better...thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources