TSQL Error: A RETURN statement with a return value cannot be used in this context

Question

I am just trying to create a function that returns a select statement, but it gives the error:

A RETURN statement with a return value cannot be used in this context.

This is my code:

CREATE FUNCTION [dbo].[Sample] (@SampleValue int)
RETURNS TABLE
AS
BEGIN
RETURN(
 SELECT * FROM View_sls
)

Please let me know the solution

score 10 · Answer 1 · answered Jun 05 '13 at 12:17

Two things:

you need to define the structure of the table you want to return
you need to add data into that table

Then you can call RETURN; to return that table's data to the caller.

So you need something like this:

CREATE FUNCTION [dbo].[Sample] (@SampleValue int)
RETURNS @returnTable TABLE 
                     (ContactID int PRIMARY KEY NOT NULL, 
                      FirstName nvarchar(50) NULL, 
                      LastName nvarchar(50) NULL, 
                      JobTitle nvarchar(50) NULL, 
                      ContactType nvarchar(50) NULL)
AS 
BEGIN
    INSERT INTO @returnTable
        SELECT ContactID, FirstName, LastName, JobTitle, ContactType
        FROM dbo.View_sls

    RETURN;
END

My table view_sls is really big, so I don't want to define all the columns one by one, is there an alternative ? — HOY, Jun 05 '13 at 12:20

score 6 · Accepted Answer · answered Jun 05 '13 at 12:38

6

Wrong syntax, that's all. You don't need BEGIN when you have an "inline table-valued function"

See CREATE FUNCTION and example B

CREATE FUNCTION [dbo].[Sample] (@SampleValue int)
RETURNS TABLE
AS
RETURN
(
 SELECT * FROM View_sls
);
GO

answered Jun 05 '13 at 12:38

gbn

422,506
82
585
676

giving error Cannot perform alter on 'dbo.xxxx' because it is an incompatible object type – Krunal Shah Oct 12 '18 at 10:15
@KrunalShah: it's a different type of function so DROP and CREATE. Their are 3 kinds of UDFs – gbn Oct 12 '18 at 10:26
i dropped it and Created it - still giving me same error! – Krunal Shah Oct 12 '18 at 10:32
@KrunalShah: ask a new question. it's unrelated to this. – gbn Oct 12 '18 at 11:07

score 3 · Answer 3 · answered Jun 05 '13 at 12:17

3

You need to describe the returned table, populate it, then RETURN:

CREATE FUNCTION [dbo].[Sample] (@SampleValue int)
RETURNS @RESULTS TABLE
(
  ID int, <other fields>
)
AS BEGIN

  INSERT @T
    <select ...>

  RETURN
END

answered Jun 05 '13 at 12:17

Alex K.

171,639
30
264
288

My table view_sls is really big, so I don't want to define all the columns one by one, is there an alternative ? – HOY Jun 05 '13 at 12:19

TSQL Error: A RETURN statement with a return value cannot be used in this context

3 Answers3