INLINE TABLE VALUED FUNCTION

INLINE TABLE VALUED FUNCTION

CREATE TABLE HCLCUSTOMER

(

ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

NAME VARCHAR(50),

SEX VARCHAR(50)

)

INSERT INTO HCLCUSTOMER

SELECT 'ANURAG' ,'M'

UNION ALL SELECT 'ANKITA','F'

UNION ALL SELECT 'MUKESH','M'

UNION ALL SELECT 'BISWA','M'

UNION ALL SELECT 'ANOOP','M'

UNION ALL SELECT 'SWAYAMPRABHA','F'

UNION ALL SELECT 'RITA','F'

SELECT * FROM HCLCUSTOMER

--NOW WE WANT ALL THE NAMES WHO ARE MALE/FEMALE

--NOW THIS CANT BE DONE USING SCALAR VALUED FUNCTION ...BECAUSE WE GET MORE THAN ONE RECORD

CREATE FUNCTION FN_HCLMALEFEMALECUSTOMER (@GENDER VARCHAR(10))

RETURNS TABLE

AS

RETURN

(

SELECT

      ID,NAME,SEX

FROM

      HCLCUSTOMER

WHERE SEX=@GENDER

)

--EXECUTE THE FUNCTION

SELECT * FROM FN_HCLMALEFEMALECUSTOMER('M')

--O/P

ID    NAME       SEX

1     ANURAG      M

3     MUKESH      M

4     BISWA       M

5     ANOOP       M

SELECT * FROM FN_HCLMALEFEMALECUSTOMER('F')

--O/P

ID    NAME              SEX

2     ANKITA            F

6     SWAYAMPRABHA      F

7     RITA              F

--SO THE INLINE FUNCTION DOESNT HAVE ==> BEGIN END BLOCK [NO BEGIN..NO END]

--IT RETURNS TABLE

--STRUCTURE OF TABLE IS DETERMINED BY THE RETURN AS BELOW

RETURN

(

SELECT

      ID,NAME,SEX

FROM

      HCLCUSTOMER

WHERE SEX=@GENDER

)

--YOU CAN ALSO DO LIKE BELOW

SELECT * FROM FN_HCLMALEFEMALECUSTOMER('M')

WHERE NAME LIKE '%ANU%'

O/P

ID    NAME        SEX

1     ANURAG      M