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

Answer 1

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