Click here to Skip to main content
11,714,345 members (82,242 online)
Click here to Skip to main content

How to pass multiple records to a Stored Procedure

, 2 Dec 2008 CPOL 75.3K 914 89
Rate this:
Please Sign up or sign in to vote.
How to pass multiple records to a Stored Procedure in a single roundtrip.

Introduction

This article describes how to use table-valued parameters when calling Stored Procedures. Table-valued parameters were introduced in SQL Server 2008. With this parameter type, a table with several rows can be passed to a Stored Procedure or a function. In some cases, this technique eliminates the need for several roundtrips between the client and the database, if the same procedure is called for several times but with different parameter values.

This article is not to be taken as an example of how to use SQL Server specific classes in C#, and certainly not as a coding style reference.

Type definitions

The example uses two types: ArtistType and RecordType. These types define the structure for parameters later. The definitions are:

-- Create the type for artist
CREATE TYPE ArtistType AS TABLE (
   [Artist#] int,
   [Name]    nvarchar(100)
);
-- Create the type for record
CREATE TYPE RecordType AS TABLE (
   [Record#] int,
   [Artist#] int,
   [Name]    nvarchar(100),
   [Year]    int
);

Table definitions

There are two target tables that are filled by a procedure. In this example, the data from the table isn't modified, but there's one trick: The client defines a primary key for each artist and record, and also a foreign key from the record to the artist. This information is used in the Stored Procedure, but the real primary keys in the database are auto-generated by SQL Server. The tables are:

-- Create artist table
CREATE TABLE Artist (
   [Artist#] int           NOT NULL IDENTITY(1,1) PRIMARY KEY,
   [Name]    nvarchar(100) NOT NULL
);
-- Create record table
CREATE TABLE Record (
   [Record#] int           NOT NULL IDENTITY(1,1) PRIMARY KEY,
   [Artist#] int           NOT NULL FOREIGN KEY REFERENCES Artist([Artist#]),
   [Name]    nvarchar(100) NOT NULL,
   [Year]    int           NULL
);

The procedure

The procedure consists of two loops. The outer loop fetches each artist and inserts it into the database. After that, it takes the identity given to the new row. After this, all the records from this single artist are fetched, and the foreign key is set to the corresponding primary key in the artist table.

CREATE PROCEDURE [dbo].[AddShoppings](
   @Artists dbo.ArtistType READONLY,
   @Records dbo.RecordType READONLY) AS
BEGIN
   -- variables to use
   DECLARE @artist         int;
   DECLARE @artistIdentity int;
   DECLARE @name           varchar(100);
   DECLARE @year           int;

   -- cursor for artists parameter
   DECLARE artistCursor CURSOR FOR 
        SELECT [Artist#], [Name]
        FROM @Artists;

   -- loop through artists
   OPEN artistCursor;
   FETCH NEXT FROM artistCursor INTO @artist, @name;
   WHILE @@FETCH_STATUS = 0
   BEGIN
      -- insert the artist
      INSERT INTO Artist ([Name]) VALUES (@name);
      SET @artistIdentity= @@IDENTITY;

      -- cursor for records parameter
      DECLARE recordsCursor CURSOR FOR 
         SELECT [Name], [Year]
         FROM @Records
         WHERE [Artist#] = @artist;

      -- fetch records and insert them
      OPEN recordsCursor;
      FETCH NEXT FROM recordsCursor INTO @name, @year;
      WHILE @@FETCH_STATUS = 0
      BEGIN
         INSERT INTO Record ([Artist#], [Name], [Year]) 
            VALUES (@artistIdentity, @name, @year);
         FETCH NEXT FROM recordsCursor INTO @name, @year;
      END;
      CLOSE recordsCursor;
      DEALLOCATE recordsCursor;

      FETCH NEXT FROM artistCursor INTO @artist, @name;
   END;
   CLOSE artistCursor;

   -- clean-up
   DEALLOCATE artistCursor;
END;

The C# code

The program is a simple console application. It:

  • Builds and fills data tables for Artist and Record
  • Creates a connection
  • Creates the database objects
  • Begins a transaction
  • Calls the procedure
  • Commits work

In order to use the code, you need to install a SQL Server 2008 instance and create a database in it. After that, the SQL Server instance name and database name are configured via app.config. It would look something like:

...
<applicationSettings>
    <TableValuedParameters.Properties.Settings>
        <setting name="DataSource" serializeAs="String">
            <value>MyMachine\SqlServerInstanceName</value>
        </setting>
        <setting name="DatabaseName" serializeAs="String">
            <value>DatabaseNameToUse</value>
        </setting>
    </TableValuedParameters.Properties.Settings>
</applicationSettings>
...

The actual call to the database is simple. The keyword for the parameters is System.Data.SqlDbType.Structured. This tells the SQL client that the data is in table format, and based on this, a DataTable object with all of its contents can be used as a parameter:

command.CommandText = "AddShoppings";
command.CommandType =  System.Data.CommandType.StoredProcedure;

parameter = command.Parameters.AddWithValue("@Artists", artist);
parameter.SqlDbType = System.Data.SqlDbType.Structured;

parameter = command.Parameters.AddWithValue("@Records", record);
parameter.SqlDbType = System.Data.SqlDbType.Structured;

command.Transaction = transaction;
command.ExecuteNonQuery();

That's about it. The rest of the logic is in the code sample. Enjoy!

History

  • December 2, 2008: Created.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mika Wendelius
Architect
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.

However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).

For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

You may also be interested in...

Comments and Discussions

 
Questionnice article Pin
iamonweb11-Jul-13 0:22
memberiamonweb11-Jul-13 0:22 
QuestionThanks Pin
zoozjar27-Feb-13 18:11
memberzoozjar27-Feb-13 18:11 
SuggestionWhy use TWO cursors? Pin
gbetsos15-Oct-12 2:23
membergbetsos15-Oct-12 2:23 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:09
mvpKanasz Robert24-Sep-12 6:09 
GeneralRe: My vote of 5 Pin
Mika Wendelius25-Sep-12 8:09
mvpMika Wendelius25-Sep-12 8:09 
GeneralMy vote of 5 Pin
CS140115-Dec-11 19:38
memberCS140115-Dec-11 19:38 
GeneralRe: My vote of 5 Pin
Mika Wendelius16-Dec-11 10:40
memberMika Wendelius16-Dec-11 10:40 
GeneralMy vote of 3 Pin
Mel Padden6-Nov-11 0:12
memberMel Padden6-Nov-11 0:12 
GeneralRe: My vote of 3 Pin
Mika Wendelius16-Nov-11 17:36
memberMika Wendelius16-Nov-11 17:36 
GeneralMy vote of 5 Pin
Filip D'haene25-May-11 6:21
memberFilip D'haene25-May-11 6:21 
GeneralRe: My vote of 5 Pin
Mika Wendelius30-May-11 10:31
memberMika Wendelius30-May-11 10:31 
Generalthanks ! Pin
aicha20089-Dec-09 4:39
memberaicha20089-Dec-09 4:39 
GeneralGreat! Pin
Member 33880636-Oct-09 23:51
memberMember 33880636-Oct-09 23:51 
GeneralDon't Use @@Identity Pin
KevinAG9-Dec-08 11:54
memberKevinAG9-Dec-08 11:54 
GeneralRe: Don't Use @@Identity Pin
Mika Wendelius10-Dec-08 10:11
memberMika Wendelius10-Dec-08 10:11 
QuestionCursors? Pin
neil_b8-Dec-08 22:01
memberneil_b8-Dec-08 22:01 
AnswerRe: Cursors? Pin
Mika Wendelius9-Dec-08 5:23
memberMika Wendelius9-Dec-08 5:23 
GeneralTry this if you want a solution for SQL 2000 Pin
Muffadal2-Dec-08 23:51
memberMuffadal2-Dec-08 23:51 
GeneralRe: Try this if you want a solution for SQL 2000 Pin
Mika Wendelius3-Dec-08 7:30
memberMika Wendelius3-Dec-08 7:30 
GeneralMy vote of 1 Pin
Paresh Gheewala2-Dec-08 19:31
memberParesh Gheewala2-Dec-08 19:31 
QuestionRe: My vote of 1 Pin
Hamed Mosavi2-Dec-08 19:52
memberHamed Mosavi2-Dec-08 19:52 
GeneralGreat Article Pin
Nagaraj Muthuchamy2-Dec-08 18:48
memberNagaraj Muthuchamy2-Dec-08 18:48 
GeneralRe: Great Article Pin
Mika Wendelius3-Dec-08 7:28
memberMika Wendelius3-Dec-08 7:28 
GeneralBrilliant! Pin
Hamed Mosavi2-Dec-08 18:35
memberHamed Mosavi2-Dec-08 18:35 
GeneralRe: Brilliant! Pin
Mika Wendelius2-Dec-08 18:45
memberMika Wendelius2-Dec-08 18:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 2 Dec 2008
Article Copyright 2008 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid