Sunday, March 30, 2014

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
)

No comments:

Post a Comment