Multistatement Scalar Valued Function

For a Multistatement Scalar-valued Function, the function body, defined in a BEGIN...END block, contains a series of T-SQL statements.
The parameter returned from the function is a scalar value.


/* Code : MSSV Function containing multiple statement */
CREATE FUNCTION MyFunc_SV_MultiStmnt( @inEmployeeKey INT )
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @temp_var VARCHAR(15)
SELECT @temp_var = FirstName
FROM dbo.DimEmployee
WHERE EmployeeKey=@inEmployeeKey

RETURN (@temp_var)
END


/* Call above MSSV function */
SELECT dbo.MyFunc_SV_MultiStmnt(2)


** Unlike the function call for Multistatement Table-Valued Function, where the function name is mentioned in the from clause, the function call for Multistatement Scalar-Valued Function doesn't contain any from clause.
** The function name is a part of the SELECT clause.