Understanding SQL User Defined Functions in SQL Server with more practical examples

This is simple User Defined Function which can be called inside stored procedure.There are 2 types of udf
a)Scalar function
b)Table valued function

Scalar function:

It accepts one or more parameters and return a single value Example:

                   CREATE FUNCTION udf_GetAllUsers(@pUserName varchar(250))  
                    RETURNS VARCHAR(50)  
                    AS  
                    BEGIN  
                        RETURN (SELECT UserName,UserLocation,Email FROM USERS WHERE UserName=@pUserName)  
                    END 

                
Table Valued Function:

It accepts one or more parameters and returns a Table. Example:

                   CREATE FUNCTION udf_GetAllUsers(@pUserName varchar(250))  
                    RETURNS table
                    AS  
                    BEGIN  
                        RETURN (SELECT * FROM USERS WHERE UserName=@pUserName)  
                    END 
                
How to execute User Defined Function in Sql Server?

select * from udf_getallusers('Shiva')