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:
Now let’s create a stored procedure that accepts that type:
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:
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!
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
ENDIt 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!
! I am proud to know him! Of course also a huge gratitude to my family for supporting me in all mine initiatives!