{"id":57055,"date":"2019-05-28T14:54:38","date_gmt":"2019-05-28T09:24:38","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=57055"},"modified":"2021-12-04T12:59:55","modified_gmt":"2021-12-04T07:29:55","slug":"sql-stored-procedures-in-sap-hana","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/","title":{"rendered":"SQL Stored Procedures in SAP HANA &#8211; An Essential Application for SAP HANA"},"content":{"rendered":"<p>In our DataFlair series of learning application of SQL in SAP HANA, our last topic is SQL stored procedures in SAP HANA. So, let&#8217;s start by taking an insight into SQL stored procedures.<\/p>\n<h3>What are SQL Stored Procedures?<\/h3>\n<p><em>An SQL stored procedure is a set or group of SQL statements with a user-assigned name.<\/em> This collection of SQL statements is known as a procedure and is stored in the RDBMS as a group of reusable statements.<\/p>\n<p>We use these stored procedures primarily to access and modify the data residing in databases in a certain way. One major advantage of SQL stored procedures is that they can be used in multiple databases and can be shared by multiple programs.<\/p>\n<h3>SAP HANA SQL Stored Procedures<\/h3>\n<p>Following the basic concept of stored procedures, SAP HANA SQL stored procedures are also a unit or module i.e. a set of SQL statements made to perform a specific task. In SAP HANA, stored procedures are used to creating modular designs for large programs with complex designs.<\/p>\n<p>Also, a different procedure can be called by another procedure. This process is the <strong>calling program<\/strong>. SQL procedures stores SQL statements aimed at performing a specific task at the database according to the proper process flow.<\/p>\n<p>In SAP HANA, the SQL stored procedures can be created as read-only procedures or read-write procedures. Also, the stored procedures can be created at two levels in SAP HANA, at a <strong>Schema Level<\/strong> (in Catalog Node) or at a <strong>Package Level<\/strong> (in Content Node) depending on the user\u2019s preference.<\/p>\n<p>The task stored in the stored procedure will perform itself on the database whenever the set of statements are being executed in an SAP HANA application.<\/p>\n<h3>Key Benefits of SQL Stored Procedures<\/h3>\n<p>Some advantages of SQL stored procedures that work in favor of SAP HANA are as follows:<\/p>\n<ul>\n<li>The stored procedures in SAP HANA ensures performance optimization as the program execution occurs in a systematic way. The result of the execution of one set of SQL statement or procedure will determine the execution of the next set of statements.<\/li>\n<li>SQL stored procedures ease the process of scripting for SAP HANA users. As it is not necessary for the users to sneak under the hood and understand all the logical complexity behind SQL scripting to apply on the database. The users can just use the pre-created SQL procedure by knowing what task a procedure performs on the database.<\/li>\n<li>Users can just call a procedure in a program by using its procedure name. Every procedure follows a business logic and performs a specific task on the database.<\/li>\n<li>The SQL procedures return data as <em>integers, characters or cursor variables<\/em> in the form of output parameters.<\/li>\n<\/ul>\n<h3>Syntax of SQL Stored Procedure in SAP HANA<\/h3>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CREATE PROCEDURE &lt;proc_name&gt; [(&lt;parameter_clause&gt;)] [LANGUAGE &lt;lang&gt;]\r\n[SQL SECURITY &lt;mode&gt;] [DEFAULT SCHEMA &lt;default_schema_name&gt;]\r\n[READS SQL DATA [WITH RESULT VIEW &lt;view_name&gt;]] AS\r\n{BEGIN [SEQUENTIAL EXECUTION]\r\n&lt;procedure_body&gt;\r\nEND\r\n| HEADER ONLY }<\/pre>\n<h4>Syntax Elements of Stored Procedure<\/h4>\n<ul>\n<li><strong>&lt;proc_name&gt;<\/strong> is the name of the procedure.<\/li>\n<li><strong>&lt;parameter_clause&gt;<\/strong> defines parameters such as IN, OUT and INOUT.<\/li>\n<li><strong>LANGUAGE &lt;Lang&gt;<\/strong> defines the programming language for the procedure. The default language is <em>SQLSCRIPT<\/em>.<\/li>\n<li><strong>SQL SECURITY&lt;mode&gt;<\/strong> defines the security mode for the procedure. There are two security modes; <em>DEFINER<\/em> and <em>INVOKER<\/em>. The default is DEFINER.<\/li>\n<li><strong>&lt;default_schema_name&gt;<\/strong> specifies the schema for unqualified objects in the procedure body. If no schema is specified, then the current session\u2019s schema is taken.<\/li>\n<li>We use <strong>READS SQL DATA<\/strong> to mark a procedure as a read-only procedure. It cannot perform modifications in the database but we can only use it to call other read-only procedures.<\/li>\n<li><strong>WITH RESULT VIEW&lt;view_name&gt;<\/strong> specifies a result view which is used as the output of other read-only procedure.<\/li>\n<li>We use <strong>SEQUENTIAL EXECUTION<\/strong> to force a sequential execution of the procedure logic.<\/li>\n<li><strong>&lt;procedure body&gt;<\/strong> contains the main body of the procedure in the language selected.<\/li>\n<li>We use <strong>HEADER ONLY<\/strong> to create procedure properties with <em>OID<\/em>.<\/li>\n<\/ul>\n<h3>How to Create SQL Stored Procedures in SAP HANA?<\/h3>\n<p>Follow these easy steps to create a stored procedure in SAP HANA:<\/p>\n<p><strong>Step 1:<\/strong> Open the SAP HANA Modeler. Expand the <strong>Content<\/strong> folder.<\/p>\n<p>Right-click on the package name &gt; <strong>New<\/strong> and select <strong>Procedure<\/strong>\u2026<\/p>\n<p><strong>Step 2:<\/strong> Enter the details specific to the new procedure such as <em>Name<\/em> (for instance, DataFlair1), <em>Description, Default Schema, Package, Run rights, Access Modes, Languages.<\/em><\/p>\n<p>Click on <strong>Finish<\/strong>.<\/p>\n<p>A script view pane will open where you write your SQL script for stored procedures. The general script is:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">PROCEDURE \u201c&lt;procedure_name&gt;\u201d ( )\r\nLANGUAGE SQLSCRIPT\r\nSQL SECURITY INVOKER\r\n\r\n-- DEFAULT SCHEMA &lt;default_schema_name&gt;\r\nREADS SQL DATA AS\r\n\r\nBEGIN\r\n\r\n\/**************************************\r\nWrite your procedure logic\r\n\r\n************************************\/\r\n\r\nEND<\/pre>\n<p><strong>Step 3:<\/strong> Add a SELECT statement between BEGIN and END.<\/p>\n<p>Save the script by clicking on the (green tick icon) Save button.<\/p>\n<p><strong>Step 4:<\/strong> Click on the <strong>Execute<\/strong> button to execute the script.<\/p>\n<p>Upon successful execution of the script, the new stored procedure can be seen under the <strong>Procedures<\/strong> folder of the <strong>Content<\/strong> node under your schema.<\/p>\n<h3>Summary<\/h3>\n<p>This concludes our tutorial on SQL stored procedures and its significance in SAP HANA. After completing this tutorial, you have learned all the uses of SQL in SAP HANA. We hope you are clear with all the concepts.<\/p>\n<p>If you have any queries related to SQL stored procedures or SAP HANA, do leave your comments below. We will be happy to help.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In our DataFlair series of learning application of SQL in SAP HANA, our last topic is SQL stored procedures in SAP HANA. So, let&#8217;s start by taking an insight into SQL stored procedures. What&#46;&#46;&#46;<\/p>\n","protected":false},"author":6,"featured_media":57078,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18002],"tags":[19927,19925],"class_list":["post-57055","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sap-hana","tag-sql-stored-procedure-benefits-for-sap-hana","tag-sql-stored-procedures-in-sap-hana"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Stored Procedures in SAP HANA - An Essential Application for SAP HANA - DataFlair<\/title>\n<meta name=\"description\" content=\"Explore the SAP HANA SQL stored procedures concept, syntax and process to create SQL stored procedure in SAP HANA. In SAP HANA, stored procedures are used to create modular designs for large programs with complex designs.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Stored Procedures in SAP HANA - An Essential Application for SAP HANA - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Explore the SAP HANA SQL stored procedures concept, syntax and process to create SQL stored procedure in SAP HANA. In SAP HANA, stored procedures are used to create modular designs for large programs with complex designs.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-28T09:24:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-04T07:29:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Stored-Procedures-in-SAP-HANA.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"802\" \/>\n\t<meta property=\"og:image:height\" content=\"420\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Stored Procedures in SAP HANA - An Essential Application for SAP HANA - DataFlair","description":"Explore the SAP HANA SQL stored procedures concept, syntax and process to create SQL stored procedure in SAP HANA. In SAP HANA, stored procedures are used to create modular designs for large programs with complex designs.","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:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/","og_locale":"en_US","og_type":"article","og_title":"SQL Stored Procedures in SAP HANA - An Essential Application for SAP HANA - DataFlair","og_description":"Explore the SAP HANA SQL stored procedures concept, syntax and process to create SQL stored procedure in SAP HANA. In SAP HANA, stored procedures are used to create modular designs for large programs with complex designs.","og_url":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2019-05-28T09:24:38+00:00","article_modified_time":"2021-12-04T07:29:55+00:00","og_image":[{"width":802,"height":420,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Stored-Procedures-in-SAP-HANA.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Stored Procedures in SAP HANA &#8211; An Essential Application for SAP HANA","datePublished":"2019-05-28T09:24:38+00:00","dateModified":"2021-12-04T07:29:55+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/"},"wordCount":861,"commentCount":2,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Stored-Procedures-in-SAP-HANA.jpg","keywords":["SQL Stored Procedure Benefits for SAP HANA","SQL Stored Procedures in SAP HANA"],"articleSection":["SAP HANA Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/","url":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/","name":"SQL Stored Procedures in SAP HANA - An Essential Application for SAP HANA - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Stored-Procedures-in-SAP-HANA.jpg","datePublished":"2019-05-28T09:24:38+00:00","dateModified":"2021-12-04T07:29:55+00:00","description":"Explore the SAP HANA SQL stored procedures concept, syntax and process to create SQL stored procedure in SAP HANA. In SAP HANA, stored procedures are used to create modular designs for large programs with complex designs.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Stored-Procedures-in-SAP-HANA.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SQL-Stored-Procedures-in-SAP-HANA.jpg","width":802,"height":420,"caption":"SQL Stored Procedures in SAP HANA topics"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-stored-procedures-in-sap-hana\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SAP HANA Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sap-hana\/"},{"@type":"ListItem","position":3,"name":"SQL Stored Procedures in SAP HANA &#8211; An Essential Application for SAP HANA"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/57055","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=57055"}],"version-history":[{"count":11,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/57055\/revisions"}],"predecessor-version":[{"id":104886,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/57055\/revisions\/104886"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/57078"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=57055"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=57055"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=57055"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}