Avatar of doramail05
doramail05
Flag for Malaysia asked on

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

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

Microsoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
doramail05

8/22/2022 - Mon
junkymail1

Add END statement right before GO
doramail05

ASKER
Nope, can't still
dqmq

And a BEGIN right after AS
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sara bhai

before create function use GO and then try it
Sara bhai

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

doramail05

ASKER
Giving error 'a return statement with a return value cannot be used in this context'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
doramail05

ASKER
Removing the 'begin' assist
junkymail1

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

Anthony Perkins

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
doramail05

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
doramail05

ASKER
solved