title: Passing an object collection as a parameter into SQL Server stored procedure
body: <p>I have a general question on whether something can be done - and whether it will be the most efficient way of doing it !</p>
  
  <p>To summarise: can I pass an object collection as a parameter to a stored procedure?</p>
  
  <p>Let's say that I have a SQL Server table called <code>Users [UserID, Forename, Surname]</code>
  and another table called <code>Hobbies [HobbyID, UserID, HobbyName, HobbyTypeID]</code></p>
  
  <p>This set up is to record multiple hobbies against a user.</p>
  
  <p>In my application, I want to update the user record.</p>
  
  <p>Normally - I would update the user table and then in code, loop through each hobby and update the hobbies table record by record.  </p>
  
  <p>If I'm updating the user forename and 2 of their hobbies, this would require 3 calls to the database. </p>
  
  <p>(1 call to a stored procedure to update the forename/surname, and 2 calls to a stored procedure to update the 2 hobby records)</p>
  
  <p>My question is:<br>
  Can I make just 1 call to the database by passing all the parameters to just 1 stored procedure. </p>
  
  <p>eg.</p>
  
  <pre><code>intUserID = 1
  strForename = "Edward"
  strSurname = "ScissorHands"
  
  dim objHobbyCollection as New List(Of Hobby)
  'Assume that I have 2 hobby objects, each with their hobbyID, UserID, HobbyName &amp; HobbyTypeID
  
  Dim params As SqlParameter()
  params = New SqlParameter() {
      New SqlParameter("@UserID", intUserID),
      New SqlParameter("@Forename", strForename),
      New SqlParameter("@Surname", strSurname),
      New SqlParameter("@Hobbies", objHobbyCollection) 
      }
  </code></pre>
  
  <p>Can I do this ? (and which way would be more efficient?)
  What would the Stored Procedure look like ?</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[User_Update]
  
   @UserID    INT
  ,@Forename      NVARCHAR(50) = NULL
  ,@Surname   NVARCHAR(50) = NULL
  ,@Hobbies   ??????????????
  </code></pre>