table valued parameter

The scenario is, you want to create a stored procedure that returns the records relating to a finite set of keys/IDs that you pass in to it. How do you do it? Well you could use dynamic SQL, but for the purpose of this article I’m going to ignore that option. Test scenario -- Create basic customer table CREATE TABLE [dbo].[Customer] ( Id INTEGER IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(50) NOT NULL ); GO -- Populate Customer table with 100,000 sample records DECLARE @Counter INTEGER SET @Counter = 1 WHILE (@Counter <= 100000) BEGIN INSERT Customer (Name) VALUES ('Test Customer #' + CAST(@Counter AS VARCHAR(10))) SET @Counter = @Counter + 1 END Option 1 - CSV list of keys In SQL Server 2000, you would most likely have ended up passing in a CSV list of keys, splitting that out into a table, and then joining that on to your real data table to match the records to return. [Read More]