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

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'PhoneList')

           DROP FUNCTION PhoneList

GO

 

CREATE FUNCTION PhoneList(@Dept_Code numeric(5))

RETURNS @Result TABLE

(Emp_Code numeric(5),

  Emp_Name varchar(50),

  Phone varchar(50)

)

AS

BEGIN

   INSERT INTO @Result (Emp_Code, Emp_Name, Phone)

   (SELECT Emp_Code, Emp_Name, Phone FROM EMP

   WHERE Dept_Code = @Dept_Code)

   RETURN

END

GO

 

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

* * * * *


Email Your Comment To AUTHOR