Only functions and extended stored procedures can

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 2005 Forums

Transact-SQL (2005)

Only functions and extended stored procedures can

Author

Topic

ZenRoe
Starting Member

14 Posts

Posted - 2008-06-18 : 03:42:29

Hi all,
In one of my UDF I use the following functions:

.....
and len(@int_date) = 4
and isnumeric(substring(@int_date,5,6)) = 1

when I use the function I get

Only functions and extended stored procedures can be executed from within a function.

Yes, when I comment the two lines the function works fine.
Ehm.... why can't I use these functions in my function ?
Thanks: Peter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 04:06:23

Both of them cannot be true.

First case: LEN(@param) = 4
Second case: SUBSTRING(@param, 5, 6) does not exists if len(@param) = 4.

Tell us what you want to do instead and maybe we can help you.

E 12°55'05.25"
N 56°04'39.16"

ZenRoe
Starting Member

14 Posts

Posted - 2008-06-18 : 04:15:37

Basically I wanna make sure, the length of the string is 4 and numeric.
However, meanwhile I figured out the problem: The input value was NULL which caused
MSSQL to stumble. I now filter out the nulls before calling the function and everything
works just fine now. The exception message thrown by MSSQL is a bit weirdo !
Greetings to Sweden !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 04:25:15

[code]DECLARE @params table (d varchar(20))

insert @params
select null union all
select '12321311' union all
select '3123' union all
select '234g'

SELECT d,
CASE
WHEN LEN(ISNULL(d, '')) = 4 AND d NOT LIKE '%[^0-9]%' THEN 'Valid'
ELSE 'Invalid'
END
from @params[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources