-2

Possible Duplicate:
Getting error in SQL query with function use

I get this error when attempting to create a function:

'CREATE FUNCTION' must be the first statement in a query batch.

Error:

Incorrect syntax near the keyword 'with'.

If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Must declare the scalar variable "@sep".

This is my T-SQL code:

CREATE FUNCTION [dbo].[fn_Split] (@sep nvarchar(10), @s nvarchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS value
FROM Pieces
)
;
Improve this question
3
  • How, exactly, is this different to your previous question? Commented Sep 28, 2012 at 6:23
  • 3
    It's not just "related to previous" - it's exactly the same question. And you've not even bothered to include any of the useful information that was painstakingly gleaned from you then (i.e. that this isn't a script being run from management studio, that you're running this code from a C# application) Commented Sep 28, 2012 at 6:35
  • As a convenience to seekers, is it possible to include a link to the previous question in the marked as duplicate message box? Damien was kind enough to include the link in his comments, but this isn't a consistent practice. Commented Oct 6, 2015 at 17:24

4 Answers 4

Reset to default
1

you didn't define table structure in return type

create FUNCTION [dbo].[fn_Split] (@sep nvarchar(10), @s nvarchar(4000))
RETURNS table TABLE (pn varcahr(100), values varchar(8000))  
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
FROM Pieces
WHERE stop > 0
)     SELECT pn,
  SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS value
 FROM Pieces
)
;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Add a GO between USE AppName and the create statement

Something like

Use AppName    
GO

create FUNCTION [dbo].[fn_Split] (@sep nvarchar(10), @s nvarchar(4000)) 
RETURNS table 
AS 
RETURN ( 
WITH Pieces(pn, start, stop) AS ( 
SELECT 1, 1, CHARINDEX(@sep, @s) 
UNION ALL 
SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2)) 
FROM Pieces 
WHERE stop > 0 
) 
SELECT pn, 
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS value 
FROM Pieces 
) 
;
Improve this answer

4 Comments

The problem is (as you'll shortly find out, or if you look at their previous question) that they're not running this through SSMS or a SQL command line tool, so GO isn't available. They've previously been told that they need to separate batches themselves. I'm not sure why this new question has been asked.
but using GO does not support in C sharp so i can't use it.when i use it gives error incorrect syntax near GO ...
@Damien_The_Unbeliever now i m running it in different batches ...
@Rahul - and we can tell that how, exactly, from your question?
0 
better than you will use procedure :
CREATE procedure [dbo].[fn_Split] 
@sep nvarchar(10),
@s nvarchar(4000))
AS
begin
  your logic..
  select col,.. from table;
end
Improve this answer

1 Comment

Who upvotes this stuff? A TVF is far better as a split function because it is more versatile. You can join on it or add a where clause when selecting from it for example.
0

I got Solution for this 

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;



SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(query);                
conn.Close();

Now i can run with Go Statement in SQL query...

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.