Passing a Table as a Parameter to a Function in SQL Server

As a database developer, it’s not uncommon to encounter the need to pass complex data structures, such as tables or views, as parameters to stored procedures or functions. This can be particularly challenging when working with large datasets or when the data is dynamic.

In this article, we’ll explore how to pass a table as a parameter to a function in SQL Server. We’ll delve into the details of table types, user-defined table types (UDTs), and how to use them to optimize your database queries.

Understanding Table Types

In SQL Server, a table type is a user-defined data structure that can be used to define a set of columns with specific data types and constraints. Table types are useful when you need to pass complex data structures as parameters to stored procedures or functions.

There are two types of table types in SQL Server:

• User-Defined Table Types (UDTs): These are custom-defined table types that can be created using the CREATE TYPE statement.
• System-Defined Table Types: These are built-in table types provided by SQL Server, such as the TABLE data type.

Creating a Table Type

To create a table type, you can use the CREATE TYPE statement. Here’s an example of how to create a simple table type:

CREATE TYPE PremiumTableType AS TABLE (
    PersonId bigint,
    PeriodId int,
    PersonRole nvarchar(20)
);

This creates a new table type called PremiumTableType with three columns: PersonId, PeriodId, and PersonRole.

Using a Table Type in a Function

To use a table type in a function, you need to declare the table type as a parameter of the function. Here’s an updated version of the GetPersonPremium function that takes a table type as a parameter:

ALTER FUNCTION [document].[GetPersonPremium]
(
    @DocumentId bigint,
    @PersonId bigint,
    @PeriodId int,
    @PersonRole nvarchar(20),
    @PremiumTableType PremiumTableType readonly
)
RETURNS DECIMAL (18,2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @premiumSum decimal (18,2)

    -- Add the T-SQL statements to compute the return value here
    set @premiumSum = (select p.Premium from @PremiumType p where p.PersonId = @PersonId and p.PeriodId = @PeriodId and p.PersonRole = @PersonRole)

    -- Return the result of the function
    RETURN @premiumSum

END

Using a Table Type Variable in a Stored Procedure

To use a table type variable in a stored procedure, you need to declare the table type variable before inserting data into it. Here’s an example:

DECLARE @PremiumTableType PremiumTableType;

INSERT INTO @PremiumTableType (PersonID, PeriodId, ConcernRole, PersonPremium)
SELECT p.PersonID, pt.PeriodID, 'Intern', pt.Premium
FROM document.Document d
INNER JOIN document.Person p ON p.DocumentCalculationLayerID = dcl.DocumentCalculationLayerID
INNER JOIN document.PersonTasks pt ON pt.PersonId = p.PersonId
INNER JOIN document.PersonCalculationHelper pch ON pch.PersonTaskId = pt.PersonTaskId
INNER JOIN document.PersonTaskCalculationHelper ptch ON ptch.PersonId = p.PersonId
INNER JOIN document.PersonMarkTypes pmt ON pmt.ConcernMarkTypeID = ptch.ConcernMarkTypeId
WHERE dcl.DocumentID = @DocumentId AND p.PersonId = @PersonId AND ptch.PeriodId = @PeriodId AND pmt.Name = @PersonRole;

EXEC document.GetPersonPremium @DocumentId, @PersonId, @PeriodId, 'Intern', @PremiumTableType;

Benefits of Using Table Types

Using table types to pass complex data structures as parameters to stored procedures or functions has several benefits:

• Improved Performance: By avoiding the need to create temporary tables or views, you can improve the performance of your database queries.
• Reduced Data Volume: By passing data in a structured format, you can reduce the volume of data that needs to be transferred between applications.
• Enhanced Flexibility: Table types allow you to define complex data structures that can be easily modified or extended as needed.

Conclusion

Passing a table as a parameter to a function in SQL Server is a powerful technique for optimizing database queries and improving performance. By using user-defined table types, you can create custom data structures that are tailored to your specific needs and improve the flexibility of your applications.

Last modified on 2025-04-30