Archive

Archive for March, 2015

Table-Valued Parameters: Basics

March 24, 2015 Leave a comment

You can use table-valued parameters (TVP) to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Great news, you can use TVP with CodeFluent Entities! Let’s see how you can use TVP to bulk load rows based on a list of id.

Create a new CFQL method:

Note the usage of “[]” after the type name. The generated method is:

public static CustomerCollection LoadByIds(System.Guid[] ids)

And the generated stored procedure:

CREATE TYPE [dbo].[cf_type_Customer_LoadByIds_0] AS TABLE (
 [Item] [uniqueidentifier] NULL)
GO
CREATE PROCEDURE [dbo].[Customer_LoadByIds]
(
 @ids [dbo].[cf_type_Customer_LoadByIds_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT [Customer].[Customer_Id], [Customer].[Customer_FirstName], [Customer].[Customer_LastName], [Customer].[Customer_DateOfBirth]
    FROM [Customer]
    WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids)))

RETURN
GO

Table-Valued Parameters require SQL Server 2008. Don’t forget to change the target of the SQL Server producer to use at least this version.

Additionally set Legacy String Array Mode to false :

Happy Coding,

The R&D Team.

References: http://www.softfluent.com/documentation/Methods_WorkingWithArrays.html