{"id":731,"date":"2015-03-02T22:33:22","date_gmt":"2015-03-02T22:33:22","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=731"},"modified":"2015-08-14T12:10:37","modified_gmt":"2015-08-14T11:10:37","slug":"what-are-table-valued-parameters","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/","title":{"rendered":"#SqlServer interview question: What are table-valued parameters?"},"content":{"rendered":"<p>Table-valued parameters is a feature introduced in SQL Server 2008. In earlier versions of SQL Server it was not possible to pass a table variable into a stored procedure as a parameter.<\/p>\n<p>In SQL SERVER 2008 we can use table-valued parameters to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing in multiple parameters. <!--more-->Table-valued parameters are declared using user-defined table types. To use a table-valued Parameter we need to follow the steps shown below:<\/p>\n<ul>\n<li>\u00a0Create a table type and define the table structure.<\/li>\n<li>\u00a0Declare a stored procedure that has a parameter of\u00a0table type.<\/li>\n<li>\u00a0Declare a table type variable and reference the table\u00a0type.<\/li>\n<li>\u00a0Populate the variable using the INSERT statement.<\/li>\n<li>\u00a0We can now pass the variable to the procedure.<\/li>\n<\/ul>\n<p><strong>Thanks for reading this article,<\/strong><\/p>\n<p><strong>Next steps :<\/strong><\/p>\n<ol>\n<li><strong>Share this with your colleagues because Sharing\u00a0 is Learning<\/strong><\/li>\n<li><strong>Comment below if you need any assistance<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: right;\"><em><strong>Powered by <a href=\"http:\/\/www.codereview.co\">CodeReview &#8211; Let&#8217;s make it Better!<\/a><\/strong><\/em><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-731\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-731\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-731\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-731\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-731-69d762e860cd8' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=731&amp;origin=dotnetsql.info&amp;obj_id=76243027-731-69d762e860cd8' data-name='like-post-frame-76243027-731-69d762e860cd8'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>Table-valued parameters is a feature introduced in SQL Server 2008. In earlier versions of SQL Server it was not possible to pass a table variable into a stored procedure as a parameter. In SQL SERVER 2008 we can use table-valued parameters to send multiple rows of data to a stored procedure or a function without &hellip; <a href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">#SqlServer interview question: What are table-valued parameters?<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-731\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-731\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-731\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-731\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/what-are-table-valued-parameters\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-731-69d762e86118b' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=731&amp;origin=dotnetsql.info&amp;obj_id=76243027-731-69d762e86118b' data-name='like-post-frame-76243027-731-69d762e86118b'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[303,6],"tags":[165,97,40,163,164],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/731"}],"collection":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/comments?post=731"}],"version-history":[{"count":4,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/731\/revisions"}],"predecessor-version":[{"id":1143,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/731\/revisions\/1143"}],"wp:attachment":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/media?parent=731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/categories?post=731"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/tags?post=731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}