BEGIN END not working

[Ed]

I'm using SSMS with SQL Server 2008 and Windows 7. The following works:

CREATE FUNCTION dbo.test (@testin INT)
RETURNS TABLE
AS
RETURN
( SELECT * FROM [tempTable] )

However, if I add BEGIN END as follows,

CREATE FUNCTION dbo.test (@testin INT)
RETURNS TABLE
AS
BEGIN
RETURN
( SELECT * FROM [tempTable] )
END

I get the following error message:

Msg 178, Level 15, State 1, Procedure test, Line 7
A RETURN statement with a return value cannot be used in this context.
Msg 102, Level 15, State 31, Procedure test, Line 12
Incorrect syntax near 'BEGIN'.

According to a book I have and the online documentation, I don't see why the
BEGIN END should cause this error.

--
Ed

[Hugo Kornelis]

Hi Ed,

The RETURNS TABLE line defines this as an inline table-valued function.
And the syntax diagram in Books Online for this kind of function does
not allow BEGIN and END - just a single RETURN with a SELECT statement
to specify the table returned.

Conversely, the BEGIN and END is mandatory for the two other kinds of
T-SQL functions (scalar and multistatement tablevalued).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Ed]

Interesting. I have a book, "Beginning Transact-SQL with SQL Server 2000 and
2005" by Paul Turley, which has the following script on p. 390:

CREATE FUNCTION fnProductListBySubCategory (@SubCategoryID int = Null)
RETURNS TABLE
AS
BEGIN
If @SubCategoryID Is Null
BEGIN
RETURN
...
END
ELSE
BEGIN
RETURN
...
END
END

I don't have the sample DB for the book, so I haven't tested this script,
but based upon what you are telling me, the script is wrong...unless maybe
SQL Server 2008 (which I'm using) is different from its predecessors that the
book is about...or am I missing something else?
--
Ed

"Hugo Kornelis" wrote:

> .
>

[Charles Wang [MSFT]]

Hi Ed,
Indeed the script will not work in SQL Server 2000, 2005 and 2008. For an
inline table-valued function, you could not use BEGIN...END to define the
function body. You can refer to the following articles for more
information:
User-Defined Functions
http://msdn.microsoft.com/en-us/library/aa175085(SQL.80).aspx
CREATE FUNCTION (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186755.aspx
SQL Server User-defined Functions
http://msdn.microsoft.com/en-us/magazine/cc164062.aspx

Best regards,
Charles Wang

[Hugo Kornelis]

On Wed, 27 Jan 2010 10:49:01 -0800, Ed wrote:

>Interesting. I have a book, "Beginning Transact-SQL with SQL Server 2000 and
>2005" by Paul Turley, which has the following script on p. 390:
>
>CREATE FUNCTION fnProductListBySubCategory (@SubCategoryID int = Null)
> RETURNS TABLE
>AS
>BEGIN
> If @SubCategoryID Is Null
> BEGIN
> RETURN
> ...
> END
>ELSE
> BEGIN
> RETURN
> ...
> END
>END
>
>I don't have the sample DB for the book, so I haven't tested this script,
>but based upon what you are telling me, the script is wrong...unless maybe
>SQL Server 2008 (which I'm using) is different from its predecessors that the
>book is about...or am I missing something else?

Hi Ed,

In addition to Charles' answer, this script WILL work if it is a
multi-statement table-valued function. The CREATE FUNCTION line has a
slightly different format for these:

CREATE FUNCTION fnProductListBySubCategory (@SubCategoryID int = Null)

RETURNS @RetTab TABLE(Column1 int NOT NULL PRIMARY KEY, Column2 int)
AS
BEGIN;
(....)
END;

Where @RetTab can be replaced by any nname of your choice, and the
actuall columns and constraints in the table cna also be tweaked to your
liking.