MULTI STATEMENT TABLE VALUED FUNCTION

--MULTI STATEMENT 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'

CREATE FUNCTION FN_HCLGENDERWISE()

RETURNS @TABLE TABLE(ID INT,FULLNAME VARCHAR(100),GENDER VARCHAR(10))

AS

BEGIN

INSERT INTO @TABLE

SELECT ID,NAME,SEX FROM HCLCUSTOMER

RETURN

END

SELECT * FROM DBO.FN_HCLGENDERWISE()

--O/P

ID    FULLNAME                      GENDER

1     ANURAG                        M

2     ANKITA                        F

3     MUKESH                        M

4     BISWA                         M

5     ANOOP                         M

6     SWAYAMPRABHA                  F

7     RITA                          F

--SEE WE MODIFIED THE COLUMN NAME

--BEGIN AND END BLOCK EXISTS HERE

--HERE YOU SPECIFY THE STRUCTURE OF THE TABLE

--RETURNS @TABLE IS OF TYPE TABLE

               J ================= J ================ J