• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

asp.net, sql

Trying to create function but have error
'Incorrect syntax near the keyword "WITH'.
LINE 17: INCORRECT SYNTAX NEAR ')'

CREATE FUNCTION dbo.udf_Split (@Separator char(1), @String varchar(2000))
RETURNS table
AS
RETURN (
    WITH Pieces(SplitID, start, stop) AS
    (
      SELECT 1, 1, CHARINDEX(@Separator, @String)
      UNION ALL
      SELECT SplitID + 1, stop + 1, CHARINDEX(@Separator, @String, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT SplitID,
      SUBSTRING(@String, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS Value
    FROM Pieces
  )
0
Webboy2008
Asked:
Webboy2008
1 Solution
 
Nico BontenbalCommented:
No error here on SQL 2005. And the function seems to work also. Are you sure your SQL Server is 2005. When I run:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
I get:
9.00.5000.00      SP4      Express Edition with Advanced Services
What do you get?
0
 
radcaesarCommented:
It successfully created the function in my SQL Server 2005?

What is ur DB Version?
0
 
Alfred A.Commented:
This should work in both SQL Server 2005 and SQL Server 2008.

If you were trying to execute this in a script, try executing this directly in a query analyzer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now