Pass Table Variable Into Stored Procedure (Table-Valued Parameters)

Posted: November 20, 2011 in SQL Server 2008

We can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters. You can do this as follows:

-- Create table that values will be inserted into
CREATE TABLE dbo.MyTable
            (col_1 INT,
             col_2 VARCHAR(20))
GO

-- Create a table type and define the table structure
-- Stored under Database > Programmability > Types > User-Defined Table Types
CREATE TYPE MyType AS TABLE 
           (col_1 INT,
            col_2 VARCHAR(20))
GO                             

-- Create routine to read the table valued parameter
CREATE PROCEDURE dbo.MyProc
                (@MyTypeParameter MyType READONLY)
AS
BEGIN
   INSERT INTO dbo.MyTable
              (col_1,
               col_2)
   SELECT * FROM @MyTypeParameter;
END
GO   
                             
-- Declare a variable to reference the table type defined above
DECLARE @MyType AS MyType

-- Insert values into the variable
INSERT INTO @MyType
           (col_1,
            col_2)
VALUES     (1, 'One'),
           (2, 'Two'),
           (3, 'Three');

-- Now call the stored procedure passing in the table valued parameter
EXEC dbo.MyProc @MyType;

-- Select the records
SELECT * FROM dbo.MyTable;

-- Drop objects
/*
   DROP PROCEDURE dbo.MyProc;
   DROP TYPE MyType;
   DROP TABLE dbo.MyTable;
*/
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s