RETURN statements in scalar valued functions must include an argument

If we create a scalar function:

CREATE FUNCTION uspData()

RETURNS INT

AS

BEGIN

    RETURN SELECT TOP(1) Roll_No FROM Student

END

Or

CREATE FUNCTION uspData(

    @Value AS INT

)

RETURNS INT

AS

BEGIN

    IF @Value = 0

         RETURN

    ELSE

         SET @Value = @Value + 10

        

    RETURN @Value

END

We will get error message :

RETURN statements in scalar valued functions must include an argument.

Cause: In sql server, scalar function must return a single value. So we have to pass value in RETURN statement as an argument not a result of any SELECT statement.

  
In first function returning a result set while in second function, RETURN statement in IF clause not returning any value. 

Solution:

We can write above function like this:

CREATE FUNCTION uspData()

RETURNS INT

AS

BEGIN

    RETURN (SELECT TOP(1) Roll_No FROM Student)

END

Or We can use inline tabled value funcion:

CREATE FUNCTION uspData()

RETURNS TABLE

AS

RETURN(

     SELECT * FROM Student

)