Calling a stored procedure with a parameter that is a table

Question

I'm trying to call the below stored procedure but I am unsure on what to pass through one of the parameters (@UnsubscribeTypes) I've tried passing in a list but got a compile error. I'm using c#, Visual Studio 2010, web forms. Any ideas on what I should pass in when calling the stored procedure in my c# code (ado.net)?

Here is my stored procedure:

ALTER PROCEDURE [czone].[SetAccountEmailPreference] 
(
    @EmailAddress VARCHAR(255),
    @UnsubscribeTypes dbo.ListOfIDs READONLY,
    @SentEmailID INT = NULL
)
AS
   SET NOCOUNT ON;
   EXEC dbo.LogObjectExecution @@PROCID;

   DECLARE @UnsubscribeID INT = (SELECT TOP 1 UnsubscribeID 
                                 FROM Email.dbo.Unsubscribe 
                                 WHERE EmailAddress = @EmailAddress 
                                 ORDER BY UnsubscribeID DESC);

   -- Unsubscribe
   IF ((SELECT COUNT(*) FROM @UnsubscribeTypes) > 0)
   BEGIN
       IF(@UnsubscribeID IS NULL)
       BEGIN
          -- ADD UNSUBSCRIBE
          INSERT INTO Email.dbo.Unsubscribe (EmailAddress, CreatedDate) 
          VALUES (@EmailAddress, CURRENT_TIMESTAMP)     

          SET @UnsubscribeID = @@IDENTITY;
       END

       -- Remove current mappings
       DELETE FROM Email.dbo.UnsubscribeTypeMapping 
       WHERE UnsubscribeFK = @UnsubscribeID;

       -- Add new mappings
       INSERT INTO Email.dbo.UnsubscribeTypeMapping (UnsubscribeFK, UnsubscribeTypeFK, SentEmailFK)
           SELECT   
              @UnsubscribeID, ID, @SentEmailID
           FROM 
              @UnsubscribeTypes;
    END

    -- Subscribe
    ELSE IF (@UnsubscribeID IS NOT NULL)
    BEGIN
       DELETE FROM Email.dbo.Unsubscribe 
       WHERE UnsubscribeID = @UnsubscribeID;
    END

possible duplicate of [How to pass table value parameters to stored procedure from .net code](http://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code) — Kritner, Jul 28 '14 at 13:51
@user3884462 [Marking answer build your reputation](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) — MJK, Jul 14 '17 at 09:57

MJK · Answer 1 · 2014-08-14T13:40:54.590

dbo.ListOfIDs is a table type. First, find out the type in your database, then check columns. generate a datatable with rows containing the UnsubscribeTypeFK ids.

The ADO.net code (not compiled)

Creating table

DataTable dt = new DataTable("Items");
dt.Columns.Add("ID", typeof(int));
dt.Rows.Add(4);

Calling proc

    con = new SqlConnection(conStr);    
    con.Open();    
    using (con)    {                
       // Configure the SqlCommand and SqlParameter.
       SqlCommand sqlCmd = new SqlCommand("czone.SetAccountEmailPreference", con);
       sqlCmd.CommandType = CommandType.StoredProcedure;
       SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@UnsubscribeTypes", _dt); // TVP
       tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP
       //pass other parameters
       sqlCmd.ExecuteNonQuery();
    }    
    con.Close();

You will find more about Table-Valued parameters here

Calling a stored procedure with a parameter that is a table

1 Answers1