Incorrect syntax near begin in sql server while creating function

Question

I am stuck with a very petty issue that I am unable to resolve.

CREATE FUNCTION dbo.fntblPsmHaendlerDailyCostsinfodump(@pDateString varchar(8), @HaendlerID int, @TableName varchar(100),@CountryID int)
RETURNS table
AS BEGIN
RETURN select top 10 * from tblcountry
END
GO

This is giving me an error -

'Incorrect syntax near begin'.

I am not able to identify why it is giving the error.

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

Correct syntax for inline UDF:

Inline user-defined functions follow these rules:

There is no function_body delimited by BEGIN and END.

The RETURN clause contains a single SELECT statement in parentheses.

CREATE FUNCTION dbo.fntblPsmHaendlerDailyCostsinfodump(
     @pDateString varchar(8),
     @HaendlerID int,
     @TableName varchar(100),
     @CountryID int)
RETURNS table
AS
RETURN (select top 10 * from tblcountry);
GO

BEGIN and END are necessary for multistatement UDFs.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 9, 2016 at 10:08

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

4 Comments

Tanu Jain Over a year ago

I have multiple lines in function thats why i used begin and end.i have

Lukasz Szozda Over a year ago

@TanuJain I mean multistatement. Of course SELECT could be broken to multiple lines for readability

Tanu Jain Over a year ago

No i mean i have to do processing inside function like creating dynamic query and then executing it using EXEC(@SQL). Without begin and end how should i do it.

Lukasz Szozda Over a year ago

@TanuJain You cannot execute dynamic SQL inside function. Period. Check Call dynamic SQL from function

Collectives™ on Stack Overflow

Incorrect syntax near begin in sql server while creating function

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related