{"id":207,"date":"2011-09-09T00:19:28","date_gmt":"2011-09-08T18:49:28","guid":{"rendered":"http:\/\/beginsql.wordpress.com\/?p=207"},"modified":"2011-09-09T00:19:28","modified_gmt":"2011-09-08T18:49:28","slug":"table-valued-parameters-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2011\/09\/09\/table-valued-parameters-in-sql-server\/","title":{"rendered":"Table-Valued Parameters in Sql Server"},"content":{"rendered":"

This is a new feature intorduced in Sql Server 2008. Table-Valued Parameters provides option for the\u00a0Client Applications to pass multiple rows of Data to Stored Procedure.<\/p>\n

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml and pass it to the stored procedure and in Stored Procedure convert this xml to a table variable\/temporary table.<\/p>\n

In this article we will not only go over this Table-Valued Parameter we will also understand how to call the Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code.<\/p>\n

Table-Valued User Defined\u00a0Data Type<\/span>
\nFirst we need to Create a User Defined Table Type which can be reused in multiple stored procedures as input table parameter data type.<\/p>\n

CREATE TYPE dbo.CustomerTableType AS TABLE\n(\u00a0\n\u00a0[CustomerID] INT,\n\u00a0[Name]\u00a0 VARCHAR(50)\n)\nGO<\/pre>\n
Stored Procedure with Table-Valued input Parameter
\n<\/span>Now let us create a simple stored procedure which takes CustomerType User Definde Table Type which we have created previously<\/p>\n
CREATE PROCEDURE dbo.GetCustomerDetails\n(\n\u00a0@Customers AS dbo.CustomerTableType READONLY\n)\nAS\nBEGIN\n\u00a0SET NOCOUNT ON\n\n\u00a0SELECT *\n\u00a0FROM @Customers\nEND<\/pre>\n
Using Stored Procedure With Table Valued Parameter in Sql Server<\/span><\/p>\n
Declare @CustomerDetails As dbo.CustomerTableType\nInsert Into @CustomerDetails Values(1,'Basavaraj'),\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (2,'Monty'),\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (3,'Shashank')\u00a0\n\nExec dbo.GetCustomerDetails @CustomerDetails\nGO\nResult:\nCustomerID   Name\n----------- ------------\n1           Basavaraj\n2           Monty\n3           Shashank<\/pre>\n
Calling Stored Procedure with Table Valued Parameter from C# Code
\n<\/span>Below is a sample C# Code example which calls the GetCustomerDetails Stored Procedure with Table-Valued Parameter which is created in the previous section.
\n<\/span><\/p>\n
using System;\nusing System.Data;\nusing System.Data.SqlClient;\nnamespace TableValuedParameter\n{\n\u00a0\u00a0\u00a0 class Program\n\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 static void Main(string[] args)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/Create and open a connection object\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlConnection conn = new SqlConnection(\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ENTER A VALID CONNECTION STRING<\/strong>\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 conn.Open();\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/Create a command object specify the stored procedure\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlCommand cmd = new SqlCommand(\"dbo.GetCustomerDetails\", conn);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cmd.CommandType = CommandType.StoredProcedure;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/PrePare the rows of Data to be Passed to the Stored Procedure <\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n            DataTable dataTable = new DataTable(\"Customer\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dataTable.Columns.Add(\"Id\", typeof(Int32));\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dataTable.Columns.Add(\"Name\", typeof(string));\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dataTable.Rows.Add(1, \"Basavaraj\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dataTable.Rows.Add(2, \"Monty\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dataTable.Rows.Add(3, \"Shashank\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/Add the Table-Valued Parameter value to the Command Object <\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n            SqlParameter param = new SqlParameter(\"@Customers\", dataTable<\/strong>);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 param.SqlDbType = SqlDbType.Structured<\/strong>;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cmd.Parameters.Add(param);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Execute the command\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlDataReader rdr = cmd.ExecuteReader();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ iterate through results, printing each record to the console\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 while (rdr.Read())\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.WriteLine(\"Employee ID: {0} Name: {1}\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,rdr[\"CustomerID\"],rdr[\"Name\"]);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 conn.Close();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rdr.Close();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.ReadKey();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\u00a0\u00a0 }\n}\n\nResult Seen on the console: <\/strong>\nEmployee ID: 1 Name: Basavaraj\nEmployee ID: 2 Name: Monty\nEmployee ID: 3 Name: Shashank<\/pre>\n
EXECUTE Permission ON Table\u00a0Type User Defined Type: <\/span>Eventhough we have Execute permission on the Stored Procedure, we still need to give Execute permission on the Table Type User Defined Type. Below\u00a0is the syntax for granting execute persmission on the Table Type User Defined Type<\/p>\n
GRANT EXECUTE ON TYPE::<\/strong>dbo.CustomerTableType TO UserName<\/pre>\n","protected":false},"excerpt":{"rendered":"
This is a new feature intorduced in Sql Server 2008. Table-Valued Parameters provides option for the\u00a0Client Applications to pass multiple rows of Data to Stored Procedure. Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml … Continue reading Table-Valued Parameters in Sql Server<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[2,3,4],"tags":[984,986,53,61],"class_list":["post-207","post","type-post","status-publish","format-standard","hentry","category-c","category-sql-server","category-sql-server-2008-whats-new","tag-c","tag-sql-server","tag-sql-server-2008","tag-table-valued-parameter"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-3l","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=207"}],"version-history":[{"count":0,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/207\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}