What Is the Purpose of Data Type Table Type Is Used?
In SQL, the table type is a data type that allows you to define a structured collection of data. It is particularly useful when you need to pass a set of records as a parameter to stored procedures or functions.
Defining Table Types
To define a table type, you use the CREATE TYPE statement in SQL. Here is an example:
CREATE TYPE EmployeeType AS TABLE (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE
);
In this example, we created a table type called EmployeeType. It consists of four columns: EmployeeID, FirstName, LastName, and HireDate. Each column is defined with its respective data type.
Using Table Types as Parameters
The primary purpose of table types is to use them as parameters in stored procedures or functions. Let’s say we have a stored procedure that needs to accept multiple employee records at once:
CREATE PROCEDURE InsertEmployees
@Employees EmployeeType READONLY
AS
BEGIN
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
SELECT EmployeeID, FirstName, LastName, HireDate
FROM @Employees;
END;
In this example, we created a stored procedure called InsertEmployees. It takes an input parameter named @Employees of type EmployeeType. The READONLY keyword ensures that the table type parameter cannot be modified within the procedure.
Inside the stored procedure, we can use the table type parameter as if it were a regular table. We insert the records from @Employees into the Employees table using a simple INSERT INTO statement.
Passing Values to Table Type Parameters
To pass values to a table type parameter, you need to create a temporary table or a table variable that matches the structure of the table type:
DECLARE @EmployeesTable EmployeeType;
INSERT INTO @EmployeesTable (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2022-01-01'),
(2, 'Jane', 'Smith', '2022-02-01');
EXEC InsertEmployees @Employees = @EmployeesTable;
In this example, we declared a variable named @EmployeesTable of type EmployeeType. We inserted two employee records into @EmployeesTable. Finally, we executed the InsertEmployees stored procedure and passed in @EmployeesTable as the value for the @Employees parameter.
Note:
- The structure of the temporary table or table variable must match that of the table type.
- You can populate the temporary table or table variable with data from other tables, queries, or any other source.
- Table types can also be used as return types for functions.
In summary, the purpose of the table type data type is to provide a convenient way to pass structured data, such as sets of records, as parameters to stored procedures or functions in SQL. By defining a table type and using it in your code, you can improve code readability, maintainability, and performance.