CODEWITHSUNDEEP
sql-serverfunction
Denis Evseev
Denis Evseev

Reputation: 1710

RETURN statements in scalar valued functions must include an argument

Didn't manage to find an aswer for the problem. Therefore, decided to share it here. Hope it will help anybody and professionals will share more efficient solution

I created a function in MSSQL that takes a parameter and try to find corresponding record in a mapping table. The extraction of mapping record was implemented using 'With clause':

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

    RETURN SELECT TOP 1 map_value FROM active_mappings 
           WHERE n=1 AND map_key = @field
END
GO

But for a strange reason: 'RETURN statements in scalar valued functions must include an argument' error occurs

Upvotes: 1

Views: 5927

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 1

Denis Evseev
Denis Evseev

Reputation: 1710

In order to solve the problem I had to declare a variable and assign result of select into the variable and only afterwards return it:

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

    SELECT @Result = (SELECT TOP 1 map_value FROM active_mappings 
           WHERE n=1 AND map_key = @field)

     RETURN @Result
END
GO

Upvotes: 4

Related Questions