Table Valued Parameter procedures with SQL Azure

Yes, it’s supported and it’s fairly easy to use a Table Value Parameter in stored procedures with SQL Azure. And here I will show you a quick introduction on how to do this.
In order to use a table value parameter in stored procedure we first need to create a custom user defined table type (UDT). Here is my very simple table UDT:

CREATE TYPE ReferenceIds AS TABLE
(
 Id INT
)

Now let’s create a stored procedure that accepts that type:

CREATE PROCEDURE [dbo].[upGetRefIds]
(
 @references ReferenceIds readonly
)
AS
BEGIN
 SELECT Count(Id) From @references
END

It is important to note that when using UDT as parameter, it can only be input parameter, and it must be explicitly set as read only.
Finally let’s write some ADO.NET:

            using (SqlConnection con =                          new SqlConnection(                             ConfigurationManager.                             ConnectionStrings["AzureSQL"].ConnectionString))             {
                 using (SqlCommand cmd = con.CreateCommand())                 {
                     cmd.CommandText = "upGetRefIds";
                     cmd.CommandType = CommandType.StoredProcedure;
                     DataTable dt = new DataTable();
                     dt.Columns.Add("Id", typeof(int));
                     dt.Rows.Add(2);
                     dt.Rows.Add(12);
                     dt.Rows.Add(2342);






                    con.Open();



                    cmd.Parameters.AddWithValue("@references", dt);
                     var result = cmd.ExecuteScalar();
                     System.Diagnostics.Debug.WriteLine(result.ToString());
                 }
             }

Maybe you already noted – the @references parameter is passed as regular DataTable, which has 1 column defined of type integer (same as our user defined type). This is the only “special” trick to make the magic work!

That’s it. As simple as that!