“Incorrect syntax” using a table-valued function in SELECT clause of T-SQL query

Question

I have a SQL 2008 database with a compatibility level of 80 (i.e. SQL 2000). I have been able to use cross apply functions, but when I add a table-valued function, this will not allow me to proceed.

I have member IDs with the following format:

EBS_322002_0397204_e

I need to get the second piece of it dynamically, since it the first piece may not always been exactly 3 characters long; otherwise, I could use the substring function and call it a day.

This is my split function:

alter FUNCTION [dbo].[fnSplit] (@sep char(1),@string varchar(8000))
RETURNS TABLE
--WITH SCHEMABINDING
AS
RETURN (
    WITH Pieces(pn, [start], [stop]) AS (
      SELECT 1, 1, CHARINDEX(@sep, @string)
      UNION ALL
      SELECT pn + 1, [stop] + 1, CHARINDEX(@sep, @string, [stop] + 1)
      FROM Pieces
      WHERE [stop] > 0
    )
SELECT pn,
  SUBSTRING(@string, [start], CASE WHEN [stop] > 0 THEN [stop]-[start] ELSE LEN(@string) END) AS string
FROM Pieces

)

First attempt:

WITH Members AS (
    SELECT DISTINCT
    memberid
    FROM MyTable
)
SELECT * FROM Members m
CROSS APPLY dbo.fnSplit('_',m.memberid) b

Produced the error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'm'.

Second attempt (to get around the compatibility issue):

WITH Members AS (
    SELECT DISTINCT
    memberid
    FROM MyTable
)
SELECT *
FROM Members m
OUTER APPLY (
    SELECT TOP 1 string AS GroupNum
    FROM dbo.fnSplit('_', m.memberid)
    ORDER BY pn DESC
  ) Step1

Produced the following error:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'm'.

I have been beating my head against a wall for a few hours and I now, do not know what to do... any suggestions?

Possible duplicate; see http://stackoverflow.com/questions/454945/execute-table-valued-function-on-multiple-rows — Stan, Jul 27 '15 at 13:04
AFAIK, table valued parameters where introduced in 2008 therefor you can't use them when your compatibility level is set to 2000. — Zohar Peled, Jul 27 '15 at 13:04
You can't do it. In order to pass anything other than constants to a TVF you need a compatibility level of 90 or higher. My first suggestion would be to address whatever it is that requires you to have a compatibility level of 80. This is 15 years out of date. My second suggestion would be to store your data in an appropriate manner, if you have a string made up of 4 component parts of variable length, store each part in a separate column, if you have a variable number of variable length components use a 1-n relationship to store them. — GarethD, Jul 27 '15 at 13:24

“Incorrect syntax” using a table-valued function in SELECT clause of T-SQL query

0 Answers0