SQL Function Issue "The Last Statement Included Within a Function Must Be a Return Statement"

The last statement included within a function must be a return statement

Try this query

CREATE FUNCTION Calc_Price (@Country VARCHAR(100) , @Price REAL , @Tax1 REAL , @Tax2 REAL)
RETURNS REAL AS
    BEGIN
        IF @Country IN ( SELECT DISTINCT TOP 15 Country FROM dbo.SUPPLIERS )
        BEGIN
            RETURN @Price + (@Price * @Tax1)
        END                                     
            RETURN @Price + (@Price * @Tax2)    
    END

This is because using sql server you need to make sure your functions end with a return statement. The Else after the return is the line causing the error since when the function is being compiled it sees the lines END ELSE which cause it to bug out.

SQL Server : the last statement included within a function must be a return statement

I suggest you to use the standard type of writing a function that is:

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

So Your function should be like this:

(Note that I reverse the order of your checkings)

CREATE FUNCTION dbo.udf_my_func(@var1 varchar(15), @var2 varchar(30))
RETURNS bit AS  
BEGIN 
    DECLARE @ResultVar bit = 0
    IF EXISTS(SELECT 1 FROM Sample_table(nolock) WHERE var1 = @var1 AND var2 <> @var2)
    BEGIN
        SELECT @ResultVar = 0
    END
    IF EXISTS(SELECT 1 FROM Sample_table(nolock) WHERE var1 = @var1 AND var2 = @var2)
    BEGIN
        SELECT @ResultVar = 1
    END
    IF NOT EXISTS(SELECT 1 FROM Sample_table(nolock) WHERE var1 = @var1 AND var2 = 1)
    BEGIN
        SELECT @ResultVar = 0
    END
    RETURN @ResultVar
END
GO

---

But You can use many better ways like this:

CREATE FUNCTION dbo.udf_my_func(@var1 varchar(15), @var2 varchar(30))
RETURNS bit AS  
BEGIN 
    DECLARE @ResultVar bit = 0
    SELECT @ResultVar = CASE 
                            WHEN var1 = @var1 AND var2 <> @var2 THEN 0
                            WHEN var1 = @var1 AND var2 = @var2 THEN 1
                            WHEN var1 = @var1 AND var2 = 1 THEN 0
                            ELSE @ResultVar
                        END
    RETURN @ResultVar
END
GO

How to return value based on if condtion in function? SQL Server

The error really says it all - there has to be ONE final RETURN statement - so change your code to:

ALTER FUNCTION [prices].[UDF_Fn_IsPriceAvailable]
    (@Product NVARCHAR(100),
     @Region NVARCHAR(100))
RETURNS DECIMAL(20,2)
BEGIN
    DECLARE @Result DECIMAL(20,2);

    IF (@Region = N'Europe')
    BEGIN 
        SELECT @Result = ISNULL(PRICE, 0.0) 
        FROM [dbo].[tblPricesEurope]
        WHERE product = @Product
    END
    ELSE IF (@Region = N'Asia') 
    BEGIN 
        SELECT @Result = ISNULL(PRICE, 0.0) 
        FROM [dbo].[tblPricesAsia]
        WHERE product = @Product
    END

    RETURN @result;
END

Also: if you have a numeric PRICE column - why on earth does this function return a NVARCHAR result?!?!?!? I changed this to a more reasonable DECIMAL(20,2) return type.

Having trouble with select statements in a stored function

You can change your code to below, I think it will give you what you're looking for:

create function GetValue3 (@ID int)
returns nvarchar(350)
as
begin
    declare @result nvarchar(350)

    select 
        @result = p.Full_Name + ',' + c.Phone + ',' + c.Email + ',' + c.CityID
    from d_People p
        , d_Contacts c
    where c.PeopleID = p.PeopleID
        and p.PeopleID = @ID;

    return @result
end

The reason you're getting errors is because in a SELECT statement you can either assign a value to variables or return data, you can't mix both together.

Also, the value of @fullName and @phoneNumber etc. is not being set separately at the top of the SELECT and then "moves down" so that at @results = @fullname, @phonenumber, @email, @city they are visible.

Receiving following error SELECT STATEMENTS INCLUDED WITHIN A FUNCTION CANNOT RETURN DATA TO A CLIENT

You could try it this way. The variable @count was being assigned an empty string. I changed that to default the value to 0. Also, the SELECT statements were attempting to return records to the client. Now they are each assigned to the variable @count instead.

Create function mastercount
(
    @table nvarchar(50)
    , @policy nvarchar(20)
    , @firstname nvarchar(20)
    , @lastname nvarchar (20)
)    
Returns int
As
Begin
    Declare @count int=0;

    If @table ='A'
        Select @count=count(*)
        from A
        where policy = @policy and firstname = @firstname and lastname = @lastname    
    Else If @table ='B'
        Select @count=count(*)
        from A
        where policy = @policy and firstname = @firstname and lastname = @lastname    
    Else If @table ='A'
        Select @count=count(*)
        from A
        where policy = @policy and firstname = @firstname and lastname = @lastname

    Return @count;
End

Error - Select statements included within a function cannot return data to a client

First of all I think you didn't specify all requirements.
I expect that you pass the location code to the function.

your function could have a look like

CREATE FUNCTION YourFunction
(
    @locationCode varchar(2)
)
RETURNS TIME
AS
BEGIN
    DECLARE @OpenTime TIME
    DECLARE @WorkStart TIME
    SET @WorkStart = '09:00'  

    SELECT @OpenTime = [OpenTime]
    FROM [dbo].[OperationHours]
    where @locationCode = [Location Code]

    RETURN ISNULL(@OpenTime, @WorkStart)
END

RETURN statements in scalar valued functions must include an argument

Here is how this same function would look as an inline table valued function.

CREATE FUNCTION [dbo].[GetMappedValue]
(
    @field nvarchar(50) = null
)
RETURNS TABLE AS RETURN
    WITH active_mappings AS 
    (
        SELECT map_value
            , ROW_NUMBER() OVER (PARTITION BY map_key ORDER BY updated DESC) AS n
        FROM MappingTable 
        WHERE tag = 'Active'
            AND map_key = @field
    )

    SELECT TOP 1 map_value 
    FROM active_mappings 
    WHERE n = 1 
    --ORDER BY ??? You have top 1 but no order by. As such you don't know which row will be returned
GO

---EDIT---

Since you said there can only be a single value returned from MappingTable you could greatly simplify this. The entire body of your function could be this simple.

select map_value
from MappingTable mt
where tag = 'Active'
    and map_key = @field

Error while trying to return one column from SQL Server function

Your primary issues are that you are trying to SELECT straight out of the function, and you are not storing the data into variables to RETURN.

But it sounds like you actually need an inline Table Valued Function, rather than a Scalar Function, these are in any case much faster

CREATE OR ALTER FUNCTION dbo.order_value 
    (@date DATE, 
     @client VARCHAR(50), 
     @order_number VARCHAR(50))
RETURNS TABLE
AS RETURN

    SELECT 
        EKKO.EBELN, 
        SUM(CASE WHEN EKPO.NETWR = CDPOS.VALUE_NEW AND CDHDR.UDATE >= @date THEN CDPOS.VALUE_OLD ELSE EKPO.NETWR END) AS ORDER_VALUE_ADJUSTED
    FROM 
        EKKO
    INNER JOIN 
        EKPO_C AS EKPO ON EKKO.EBELN = EKPO.EBELN 
                       AND EKKO.MANDT = EKPO.MANDT
    LEFT JOIN 
        CDPOS_C AS CDPOS ON (EKPO.MANDT + EKPO.EBELN + EKPO.EBELP) = CDPOS.TABKEY
    LEFT JOIN 
        CDHDR ON CDHDR.CHANGENR = CDPOS.CHANGENR
    WHERE 
        EKKO.MANDT = @client 
        AND EKKO.EBELN = @order_number
    GROUP BY 
        EKKO.EBELN
;

An inline table function must be a single RETURN SELECT statement.

You use it like this

SELECT *
FROM dbo.order_value(GETDATE(), 'SomeClient', 'SomeOrder') ov;

Or

SELECT *
FROM dbo.Orders o
CROSS APPLY dbo.order_value(o.Date, o.Client, o.Number) ov;

---

Related Topics