TSQL Error: A RETURN statement with a return value cannot be used in this context
23,479
Solution 1
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
Solution 2
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
Solution 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
Author by
HOY
Enterpreneur, Opportunist. Computer Engineer, Software Developer
Updated on June 05, 2022Comments
-
HOY about 2 years
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
-
HOY about 11 yearsMy table view_sls is really big, so I don't want to define all the columns one by one, is there an alternative ?
-
HOY about 11 yearsMy table view_sls is really big, so I don't want to define all the columns one by one, is there an alternative ?
-
ZygD about 11 yearsI assume OP wants an inline TVF, not a multi-statement one
-
Krunal Shah over 5 yearsgiving error Cannot perform alter on 'dbo.xxxx' because it is an incompatible object type
-
ZygD over 5 years@KrunalShah: it's a different type of function so DROP and CREATE. Their are 3 kinds of UDFs
-
Krunal Shah over 5 yearsi dropped it and Created it - still giving me same error!
-
ZygD over 5 years@KrunalShah: ask a new question. it's unrelated to this.