CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
user3884462
user3884462

Reputation: 201

Calling a stored procedure with a parameter that is a table

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

Upvotes: 0

Views: 420

Answers (1)

MJK
MJK

Reputation: 3514

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

Upvotes: 2

Related Questions