In SQL Server, stored procedures are commonly used to perform complex operations on data. They can accept parameters to customize their behavior. While it is easy to pass a single parameter or even a few parameters to a stored procedure, passing a large amount of data can be cumbersome and inefficient.

Fortunately, SQL Server 2008 introduced a feature called table-valued parameters, which allows you to pass a table’s worth of data into a single parameter. This feature simplifies the process of passing and processing large amounts of data in stored procedures.

Table Types

In order to use table-valued parameters, you first need to define a table type. A table type is a user-defined data type that is based on a table structure. You can define the fields and data types for the table type based on your specific requirements.

For example, let’s say you have a table called “Employee” with fields like “FirstName”, “LastName”, and “Salary”. You can create a table type called “EmployeeTableType” that has the same fields and data types as the “Employee” table.

Using Table Types as Variables

Once you have defined a table type, you can declare a variable of that type in your stored procedure. The variable will act as a container for the data that you pass in as a table-valued parameter.

For example, if you have a table type called “EmployeeTableType”, you can declare a variable of that type like this:

DECLARE @Employees AS EmployeeTableType

You can then insert data into the variable using a SELECT statement:

INSERT INTO @Employees
SELECT FirstName, LastName, Salary
FROM Employee

By using a table-valued parameter, you can pass the entire “Employee” table into the stored procedure with just one call.

Table Types as Parameters

The advantage of using table-valued parameters becomes apparent when you need to insert data into another table based on the data in the table-valued parameter.

For example, let’s say you have a table called “MgmtTraining” that contains the approved list of classes for a company’s managers. You also have a table called “MgmtTrainingNew” that contains the list of classes that will be approved soon.

Instead of running a stored procedure multiple times to insert each record from “MgmtTrainingNew” into “MgmtTraining”, you can pass the entire “MgmtTrainingNew” table as a table-valued parameter and insert all the records at once.

Here’s an example of how you can define a table type and a stored procedure that accepts a table-valued parameter:

CREATE TYPE MgmtTrainingType AS TABLE (
  ClassName VARCHAR(50) NOT NULL,
  ClassDurationHours INT NULL
);

CREATE PROCEDURE AddNewTraining
  @TrainingData MgmtTrainingType READONLY
AS
BEGIN
  INSERT INTO MgmtTraining (ClassName, ClassDurationHours)
  SELECT ClassName, ClassDurationHours
  FROM @TrainingData
END

In this example, the stored procedure “AddNewTraining” accepts a table-valued parameter called “@TrainingData” of type “MgmtTrainingType”. The stored procedure then inserts the data from the table-valued parameter into the “MgmtTraining” table.

By using table-valued parameters, you can simplify your code and improve performance by reducing the number of database calls.

Conclusion

Table-valued parameters are a powerful feature in SQL Server that allow you to pass and process large amounts of data in a more efficient and convenient way. By defining table types and using them as variables and parameters in stored procedures, you can simplify your code and improve performance.

Do you have any questions or comments about table-valued parameters in SQL Server? Let me know in the comment section below!