Functions with returns 'table' has no begin...end?

Viewed 9756

If I ever add 'Begin' after 'As' It says incorrect syntax. It works just fine If I'm trying to return one value though.

Instead I should add 'return' after 'as' and open brackets ()

Why is that?

With one value as returns, I can do it fine:

 CREATE FUNCTION MATHEE(@A int,@B int)
 returns int
 as 
 begin
    DECLARE @C int

    SET @C = @A + @B

 return @C 
 end

With table as returns, I cant:

   CREATE FUNCTION TOP_USERSs(@number int,@bob varchar(20))
RETURNS TABLE 
AS
BEGIN

RETURN SELECT agent from Agents where agent = @bob and prime > @number
END

It says :

Incorrect syntax near 'BEGIN'.

It works fine using brackets and without begin..end though:

CREATE FUNCTION TOP_USERSs(@number int,@bob varchar(20))
RETURNS TABLE 
AS
RETURN
(
 SELECT agent from Agents where agent = @bob and prime > @number
)
4 Answers
Related