Getting error in SQL query with function use

Question

When I run this query through C# adapter it is causing an error:

Incorrect syntax near Use

Any ideas? When I run this in SQL Server 2008 R2 it's working fine.

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
)
;

/****** Object: Table [dbo].[drillDowntable1] Script Date: 09/24/2012 18:43:32 ******/
USE [master]

SET ANSI_NULLS ON
;
SET QUOTED_IDENTIFIER ON
;
SET ANSI_PADDING ON
;
CREATE TABLE [dbo].[drillDowntable1](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](20) NULL,
[json] [varchar](max) NULL,
[isActive] [bit] NOT NULL
) ON [PRIMARY]

Aaron Bertrand · Accepted Answer · 2012-09-27 14:03:25Z

5

You need a GO to put things in their own batch.

FROM Pieces
)
;

GO -- < this is important
/****** Object: Table [dbo].[drillDowntable1] Script Date: 09/24/2012 18:43:32 ******/
USE [master]

answered Sep 27, 2012 at 14:03

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Aaron Bertrand Over a year ago

@Rahul well I have no idea which C# adapter you're using to run this script, but if it doesn't understand the GO batch separator, you may have to look into its documentation to see how to correctly terminate batches. You might need to submit each object as its own batch.

Aaron Bertrand Over a year ago

@Rahul no, a semi-colon is a statement terminator, not a batch separator. Modules like functions need to be in their own batch.

Jim Over a year ago

Rahul, just split your script into two parts and run each part on its own.

Aaron Bertrand Over a year ago

@Rahul are you sure you've set your database context to the right database? Based on what you had I wouldn't be surprised if your database already has that function and a copy exists in master too.

Aaron Bertrand Over a year ago

@Rahul well, SQL Server isn't lying to you or playing a joke. Either you aren't checking for the function correctly or you're not in the database you think you are. You can also add IF EXISTS () DROP to the script so it won't matter if a version already exists, but I think you need to straighten out database context first and foremost.

|

Collectives™ on Stack Overflow

Getting error in SQL query with function use

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related