UDF returns error - 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 2005 Forums

SQL Server Administration (2005)

UDF returns error

Author

Topic

pdude
Starting Member

5 Posts

Posted - 2010-02-19 : 22:19:13

Hi

I'm getting this error when I try to run a UDF on 1 of our servers.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

I suspect that it may be due a permissions or config issue at the server level.

CREATE FUNCTION dbo.test
(@in varchar(max))
RETURNS @t TABLE
(val varchar(max))
AS
BEGIN
INSERT INTO @t SELECT substring(@in, 1,3)
RETURN
END

CREATE TABLE #x(val varchar(max))
INSERT INTO #x SELECT 'abcdefg'

--this works
SELECT * FROM #x CROSS JOIN dbo.test('abcdefg') xval
--val val
--abcdefg abcd

--but this returns an error
SELECT * FROM #x CROSS JOIN dbo.test(#x.val) xval
--Msg 102, Level 15, State 1, Line 3
--Incorrect syntax near '.'.

Any help would be appreciated
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-19 : 22:34:10

you will need to use CROSS APPLY.


--but this returns an error
SELECT * FROM #x CROSS JOIN APPLY dbo.test(#x.val) xval
--Msg 102, Level 15, State 1, Line 3
--Incorrect syntax near '.'.

KH
[spoiler]Time is always against us[/spoiler]

pdude
Starting Member

5 Posts

Posted - 2010-02-19 : 22:53:36

Hi khtan

Sorry that was a typo..
SELECT * FROM #x CROSS APPLY dbo.test(#x.val) xval
gives me the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

pd

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-19 : 23:22:00

[code]SELECT * FROM #x CROSS APPLY dbo.test(#x.val) xval[/code]
This statement is fine. No syntax error

Check your database compatibility level. It might not be in 90

this will show you the current compatibility level of your database
[code]select compatibility_level from sys.databases where name = db_name()[/code]

to change it, use the ALTER DATABASE command
http://msdn.microsoft.com/en-us/library/bb510680.aspx

If this is a live database, you should do this in a testing environment. Refer to above link for further details on difference between comp level 80 and 90

KH
[spoiler]Time is always against us[/spoiler]

pdude
Starting Member

5 Posts

Posted - 2010-02-20 : 01:09:12

Hi Khtan

You are a genius...Cheers

pd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 01:11:05

quote:
Originally posted by pdude

Hi Khtan

You are a genius...Cheers

pd

See what all you can do with APPLY operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-20 : 08:58:55

quote:
Originally posted by pdude

Hi Khtan

You are a genius...Cheers

pd

Thanks but I'm not. I just can't sleep

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources