Create Function in sql server  2005,2008:

In sql server 2005 you can create two types of functions:

1. Inline Table valued function:
1. If you want return  value as a table then use this function.
2. scalar valued function:
1. If you want return type as any data type then use this function.

1:Create Inline Table valued function:

the syntax of inline table values function is given below:

 CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(  
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)

Examples:

Approach 1:

This is a simple example of function returns  table

CREATE FUNCTION Fun_MyFirstInline
(   
    @Student_Id int
)
RETURNS TABLE
AS
RETURN
     select Student_name,Student_class from dbo.student where Student_Id =@Student_Id

Approach 2:

In this exapmle we are cheking student info if not 'Student Does not Exists' message will pass in table and we have created a table  variable  @studentInfo .

CREATE  FUNCTION Fun_MyFirstInline
(   
    @Student_Id int
)
RETURNS @studentInfo TABLE (name varchar(50),class varchar(50))
AS
begin
 
        --Insert student info into table variable @studentInfo

        insert into @studentInfo
        select   Student_name,Student_class from dbo.student where Student_Id =@Student_Id
        
--Check if any records are present is table variable @studentInfo
        declare @count int
        select @count=0
        select @count=isnull(count(*),0) from @studentInfo

 --If Records are not present in table insert a error message into the table variable
        if @count=0
        insert into @studentInfo
        select   '','Student Does not Exists' 

RETURN
end   

2:Create scalar valued function:

creating scalar valued function is more easy than inline table valued function.

syntax for scalar valued function is given below: 

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END

Examples:

Approach 1:

This is a simple example of scalar valued function creation  

CREATE FUNCTION [DBO].[GETORDERLINEPROPERTY]
(
    @STUDENT_ID INT
)       
RETURNS  NVARCHAR(MAX)
AS       
BEGIN 
   
    DECLARE @MARKS NVARCHAR(MAX)
    SET @MARKS =(SELECT MARKS FROM DBO.STUDENT_REPORT WHERE STUDENT_ID =@STUDENT_ID)
    RETURN @MARKS

END