Table-value function sql server

There are three type of function in sql server

     1)Syetem function : which is defined by system

     2)Sclar-value function : function return single value

     3)Table-value function :function return an table

    

     Table-value function

     a)Return table

     b)only return a result set directly to a user

   

       

--we aree creating a table named STUDENT

CREATE TABLE STUDENT

(

id   INT IDENTITY,

NAME VARCHAR(500),

AGE  INT

)

--we are inserting values to the STUDENT table

INSERT INTO STUDENT(NAME,AGE)

VALUES ('NAME1',17)

INSERT INTO STUDENT(NAME,AGE)

VALUES ('NAME2',20)

INSERT INTO STUDENT(NAME,AGE)

VALUES ('NAME3',25)

--we aree creating a table named STUDENT_PARENTS

CREATE TABLE STUDENT_PARENTS

(

ID INT,

PARENT_NAME VARCHAR(500)

)

--we are inserting values to the STUDENT_PARENTS table

INSERT INTO STUDENT_PARENTS(ID,PARENT_NAME)

VALUES (1,'PARENT1')

INSERT INTO STUDENT_PARENTS(ID,PARENT_NAME)

VALUES (2,'PARENT2')

--we aree creating a table named MARKS

CREATE TABLE MARKS

(

id INT IDENTITY,

STUDENT_ID INT,

SUBJECT_ID INT,

MARKS      DECIMAL(18,2)

)

--we are inserting values to the MARKS table

INSERT INTO MARKS(STUDENT_ID,SUBJECT_ID,MARKS)

VALUES (1,1,95.23)

INSERT INTO MARKS(STUDENT_ID,SUBJECT_ID,MARKS)

VALUES (1,2,90.23)

INSERT INTO MARKS(STUDENT_ID,SUBJECT_ID,MARKS)

VALUES (2,1,80.00)

INSERT INTO MARKS(STUDENT_ID,SUBJECT_ID,MARKS)

VALUES (2,2,85.21)

----------------------------------------

/*

Now, i am creating a table value function , the function insert the data to a table variable depending

on parameter which has received

*/

CREATE FUNCTION fn_GetStudentDetais

(

@student_id INT

)

RETURNS @studentData TABLE

(

   NAME                   VARCHAR(500),

   AGE                    INT ,

   PARENT_NAME    VARCHAR(500)

)

AS

BEGIN

   INSERT INTO @studentData (NAME, AGE, PARENT_NAME)

   SELECT X.NAME,X.AGE,Y.PARENT_NAME FROM STUDENT X

   INNER JOIN STUDENT_PARENTS Y ON X.ID=Y.ID

   WHERE X.ID=@student_id

   RETURN;

END;

------------------------------------------

--like sclar function we can not run directly

SELECT dbo.fn_GetStudentDetais(5)

--Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_GetStudentDetais", or the name is ambiguous.

--like sclar function we can not run directly with query

SELECT dbo.fn_GetStudentDetais(X.id) FROM STUDENT X

--Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_GetStudentDetais", or the name is ambiguous.

-----------------------------------------------------------

--we have to write either cross apply , outer apply join to fetch the data from table-value function

SELECT ML.NAME,ML.AGE,ML.PARENT_NAME,V.MARKS

FROM MARKS V

OUTER APPLY dbo.fn_GetStudentDetais(V.STUDENT_ID) ML