CROSS APPLY - 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)

CROSS APPLY

Author

Topic

fabiobarreiro
Starting Member

2 Posts

Posted - 2010-10-04 : 10:50:14

plz, what's wrong???
I'm getting a Syntax Error message

create table testeapply (
id int,
ids varchar(32)
)
GO
insert into testeapply (id, ids) values (1,'1,2,3,4')
insert into testeapply (id, ids) values (2, '13,22,31,44')
insert into testeapply (id, ids) values (3, '7,4,3,12')

select
t1.id,
t1.ids,
c.data
from
testeapply as t1 cross apply
dbo.Split(t1.ids, ',') as c -- ERROR IN THIS LINE

select * from dbo.Split('7,4,3,12',',')
This returns 4 rows...a row for each number

The code for the Split function

---------------------------------------------

CREATE FUNCTION [dbo].[Split]
(
@RowData varchar(2000),
@SplitOn varchar(5)
)
RETURNS @RtnValue table
(
Data varchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-04 : 12:07:54

If you want help, you should supply the actual error text, and the statement that gave the error.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 12:16:50

check your compatibility level of db

EXEC sp_dbcmptlevel 'your db name here'

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

fabiobarreiro
Starting Member

2 Posts

Posted - 2010-10-04 : 17:22:16

Hi guys!

Thanks for answering

Here goes the error message:
"Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '.'."

and the compatibility level:
"The current compatibility level is 80."

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-04 : 18:44:50

CROSS APPLY is not supported in SQL Server 2000 (compatibility level 80)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources