{"id":777,"date":"2018-09-30T09:20:12","date_gmt":"2018-09-30T02:20:12","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=777"},"modified":"2021-04-28T15:42:36","modified_gmt":"2021-04-28T08:42:36","slug":"sql-server-stored-procedures","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/","title":{"rendered":"SQL Server Stored Procedures"},"content":{"rendered":"\n<p>SQL Server stored procedures are used to group one or more Transact-SQL statements into logical units. The stored procedure is stored as a named object in the SQL Server Database Server.<\/p>\n\n\n\n<p>When you call a stored procedure for the first time, SQL Server creates an execution plan and stores it in the cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan to execute the stored procedure very fast with reliable performance.<\/p>\n\n\n\n<p>This tutorial series introduces you to the stored procedures and shows you how to develop flexible stored procedures to optimize database access.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h2 class=\"wp-block-heading\" id='getting-started-with-sql-server-stored-procedures'>Section 1. Getting started with SQL Server Stored Procedures <a href=\"#getting-started-with-sql-server-stored-procedures\" class=\"anchor\" id=\"getting-started-with-sql-server-stored-procedures\" title=\"Anchor for Section 1. Getting started with SQL Server Stored Procedures\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/basic-sql-server-stored-procedures\/\">A basic guide to stored procedures<\/a> &#8211; show you how to create, execute, modify, and drop a stored procedure in SQL Server.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-stored-procedure-parameters\/\">Parameters<\/a> &#8211; learn how to create stored procedures with parameters, including optional parameters.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/variables\/\">Variables&nbsp;<\/a> &#8211;&nbsp; introduce you to Transact-SQL variables and how to manipulate variables in stored procedures.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/stored-procedure-output-parameters\/\">Output Parameters<\/a>&nbsp; &#8211; guide you on how to return data from a stored procedure back to the calling program using the output parameters.<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h2 class=\"wp-block-heading\" id='control-of-flow-statements'>Section 2. Control-of-flow statements <a href=\"#control-of-flow-statements\" class=\"anchor\" id=\"control-of-flow-statements\" title=\"Anchor for Section 2. Control-of-flow statements\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-begin-end\/\">BEGIN&#8230;END<\/a> &#8211; create a statement block that consists of multiple Transact-SQL statements that execute together.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-if-else\/\">IF ELSE<\/a> &#8211; execute a statement block based on a condition.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-while\/\">WHILE<\/a> &#8211; repeatedly execute a set of statements based on a condition as long as the condition is true.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-break\/\">BREAK<\/a> &#8211; exit the loop immediately and skip the rest of the code after it within a loop.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-continue\/\">CONTINUE<\/a> &#8211;&nbsp;skip the current iteration of the loop&nbsp;immediately and continue the next one.<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h2 class=\"wp-block-heading\" id='cursors'>Section 3. Cursors <a href=\"#cursors\" class=\"anchor\" id=\"cursors\" title=\"Anchor for Section 3. Cursors\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-cursor\/\">Cursor<\/a>&nbsp; &#8211; show you how to handle cursors.<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h2 class=\"wp-block-heading\" id='handling-exceptions'>Section 4. Handling Exceptions <a href=\"#handling-exceptions\" class=\"anchor\" id=\"handling-exceptions\" title=\"Anchor for Section 4. Handling Exceptions\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-try-catch\/\">TRY CATCH<\/a> &#8211; learn how to handle exceptions gracefully in stored procedures.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-raiserror\/\">RAISERROR<\/a> &#8211; show you how to generate user-defined error messages and return it back to the application using the same format as the system error.<\/li><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-throw\/\">THROW<\/a> &#8211; walk you through the steps of raising an exception and transferring the execution to the <code>CATCH<\/code> block of a <code>TRY CATCH<\/code> construct.<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h2 class=\"wp-block-heading\" id='dynamic-sql'>Section 5. Dynamic SQL <a href=\"#dynamic-sql\" class=\"anchor\" id=\"dynamic-sql\" title=\"Anchor for Section 5. Dynamic SQL\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-dynamic-sql\/\">Dynamic SQL<\/a> &#8211; learn how to construct general-purpose and flexible SQL statements using the dynamic SQL technique.<\/li><\/ul>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.<\/p>\n","protected":false},"author":1,"featured_media":2737,"parent":0,"menu_order":6,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-777","page","type-page","status-publish","has-post-thumbnail","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Server Stored Procedures Tutorial<\/title>\n<meta name=\"description\" content=\"This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Stored Procedures Tutorial\" \/>\n<meta property=\"og:description\" content=\"This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Server Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2021-04-28T08:42:36+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/\",\"name\":\"SQL Server Stored Procedures Tutorial\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/wp-content\\\/uploads\\\/sql-server-stored-procedures.svg\",\"datePublished\":\"2018-09-30T02:20:12+00:00\",\"dateModified\":\"2021-04-28T08:42:36+00:00\",\"description\":\"This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/wp-content\\\/uploads\\\/sql-server-stored-procedures.svg\",\"contentUrl\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/wp-content\\\/uploads\\\/sql-server-stored-procedures.svg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-stored-procedures\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Stored Procedures\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\",\"name\":\"SQL Server Tutorial\",\"description\":\"The Practical SQL Server Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Stored Procedures Tutorial","description":"This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Stored Procedures Tutorial","og_description":"This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.","og_url":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/","og_site_name":"SQL Server Tutorial","article_modified_time":"2021-04-28T08:42:36+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/","url":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/","name":"SQL Server Stored Procedures Tutorial","isPartOf":{"@id":"https:\/\/www.sqlservertutorial.net\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/sql-server-stored-procedures.svg","datePublished":"2018-09-30T02:20:12+00:00","dateModified":"2021-04-28T08:42:36+00:00","description":"This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.","breadcrumb":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/#primaryimage","url":"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/sql-server-stored-procedures.svg","contentUrl":"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/sql-server-stored-procedures.svg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlservertutorial.net\/"},{"@type":"ListItem","position":2,"name":"SQL Server Stored Procedures"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlservertutorial.net\/#website","url":"https:\/\/www.sqlservertutorial.net\/","name":"SQL Server Tutorial","description":"The Practical SQL Server Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlservertutorial.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/777","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/comments?post=777"}],"version-history":[{"count":3,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/777\/revisions"}],"predecessor-version":[{"id":2791,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/777\/revisions\/2791"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/media\/2737"}],"wp:attachment":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/media?parent=777"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}