'CREATE FUNCTION must be the only statement in the batch' SQL Server 2008

doramail05
doramail05 used Ask the Experts™
on
while creating function in sql server 2008

getting :
'CREATE FUNCTION must be the only statement in the batch'

after checking, have only one CREATE Function in the whole statement
CREATE FUNCTION fnModuleResultRanking

( 

-- Add the parameters for the function here

@ModuleID BIGINT

)

RETURNS TABLE 

AS

RETURN 

(

SELECT     ROW_NUMBER() OVER (ORDER BY totalmoduleresult DESC) AS number, *
FROM         ModuleResult mr
WHERE     mid = @ModuleID AND intakeid IN (1, 2, 3);
)

GO

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Add END statement right before GO

Author

Commented:
Nope, can't still

Commented:
And a BEGIN right after AS
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
before create function use GO and then try it

Commented:
may be use
EXECUTE ('CREATE FUNCTION fnModuleResultRanking
(
@ModuleID BIGINT
)
RETURNS TABLE
AS
RETURN
(
SELECT     ROW_NUMBER() OVER (ORDER BY totalmoduleresult DESC) AS number, *
FROM         ModuleResult mr
WHERE     mid = @ModuleID AND intakeid IN (1, 2, 3)
)' )

Author

Commented:
Giving error 'a return statement with a return value cannot be used in this context'

Author

Commented:
Removing the 'begin' assist
I tested your code with a dummy database (in sql 2008) and I was able to get this to work.  I think there was some formatting issues as well as semi-colons in the wrong place.  Hopefully this should work.
CREATE FUNCTION fnModuleResultRanking(@ModuleID BIGINT)
RETURNS TABLE
AS
RETURN 
(
	SELECT	ROW_NUMBER() OVER (ORDER BY totalmoduleresult DESC) AS number, *
	FROM    ModuleResult mr
	WHERE   mid = @ModuleID AND intakeid IN (1, 2, 3)
);
GO

Open in new window

Top Expert 2012

Commented:
For the record, BEGIN..END is optional with Inline Table-valued functions.

I agree that something else is going on here.  Except for the ";" (I get" Incorrect syntax near ';'), there is no problem with your function as posted.
solved with this function without syntax error
USE [iDash5]
GO
/****** Object:  UserDefinedFunction [dbo].[fnModuleResultRanking]    Script Date: 09/03/2011 17:54:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnModuleResultRanking]

(

-- Add the parameters for the function here

@ModuleID BIGINT

)

RETURNS TABLE 

AS


RETURN 

(

SELECT     ROW_NUMBER() OVER (ORDER BY totalmoduleresult DESC) AS number, *
FROM         ModuleResult mr
WHERE     mid = @ModuleID AND intakeid IN (1, 2, 3)
)

Open in new window

Author

Commented:
solved

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial