If we create a
scalar function:
CREATE FUNCTION uspData()
RETURNS INT
AS
BEGIN
RETURN SELECT
TOP(1) Roll_No FROM
Student
END
Or
We will get error message :
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