1

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.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Tanu Jain
  • 107
  • 1
  • 3
  • 11

1 Answers1

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.

Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • I have multiple lines in function thats why i used begin and end.i have – Tanu Jain Mar 09 '16 at 10:11
  • @TanuJain I mean multistatement. Of course `SELECT` could be broken to multiple lines for readability – Lukasz Szozda Mar 09 '16 at 10:12
  • 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. – Tanu Jain Mar 09 '16 at 10:19
  • 1
    @TanuJain You cannot execute dynamic SQL inside function. Period. Check [Call dynamic SQL from function](http://stackoverflow.com/questions/9607935/call-dynamic-sql-from-function#9607970) – Lukasz Szozda Mar 09 '16 at 10:19