Programming in MS SQL Server 2012

Table Variable

Table variable is a special data type that used to stores a query result set temporarily in memory for processing at a later time. Table variable can define in user defined functions, stored procedures and program batches. Table variable's data value automatically erases when end of the function, stored procedure or program batch.
The following user defined function describes to display Department wise Employee Phone Numbers through Table Data Type.

-- Example 80 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'PhoneList')

DROP FUNCTION PhoneList

CREATE FUNCTION PhoneList(@Dept_Code numeric(5))

RETURNS @Result TABLE

(Emp_Code numeric(5),

Emp_Name varchar(50),

Phone varchar(50)

)

BEGIN

INSERT INTO @Result (Emp_Code, Emp_Name, Phone)

(SELECT Emp_Code, Emp_Name, Phone FROM EMP

WHERE Dept_Code = @Dept_Code)

RETURN

END

SELECT * FROM PhoneList(10)

SELECT * FROM PhoneList(20)

SELECT * FROM PhoneList(30)

SELECT * FROM PhoneList(40)

SELECT * FROM PhoneList(50)

Query Output Screen

More Reference URL: http://technet.microsoft.com/en-us/library/ms175010.aspx

* * * * *

Your Comment To AUTHOR