sql user defined function

Question

for a table valued function in sql why cant we write sql statements inside begin and end tags like-

create function dbo.emptable()
returns Table
as
BEGIN --it throws an error
return (select id, name, salary from employee)
END
go

while in scalar valued function we can use these tags like

create function dbo.countemp()
returns int
as
begin
return (select count(*) from employee)
end
go

is there any specific rule where we should use BEGIN & END tags

Answer 1

In an INLINE TVF (like your first example), the BEGIN and END would try to force it to be procedural code, and therefore it would no longer be an Inline TVF. Scalar functions are only available in a procedural form (which is lousy). Therefore, Scalar functions should generally be avoided in the current versions of SQL Server.

Answer 2

The multi-statement table-valued function is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement. Unlike the inline table-valued function, a table variable must be explicitly declared and defined. The following example shows how to implement a multi-statement table-valued function that populates and returns a table variable.

USE Northwind
go
CREATE FUNCTION fx_OrdersByDateRangeAndCount
( @OrderDateStart smalldatetime, 
  @OrderDateEnd smalldatetime, 
  @OrderCount smallint )
RETURNS @OrdersByDateRange TABLE
  (  CustomerID nchar(5),
     CompanyName nvarchar(40),
     OrderCount smallint,
     Ranking char(1) )
AS
BEGIN
// statements that does some processing ....

END

From the above, I guess BEGIN and END denotes the intent/use of multiple statements & hence it requires the table variable to be defined as shown in the code above.

from http://www.sqlteam.com/article/intro-to-user-defined-functions-updated