{"id":435413,"date":"2018-05-11T07:03:22","date_gmt":"2018-05-11T07:03:22","guid":{"rendered":"https:\/\/essaypaper.org\/how-to-create-a-stored-procedure-in-sql-language\/"},"modified":"2018-10-24T09:08:12","modified_gmt":"2018-10-24T09:08:12","slug":"how-to-create-a-stored-procedure-in-sql-language","status":"publish","type":"post","link":"https:\/\/www.benedictsol.com\/blogs\/how-to-create-a-stored-procedure-in-sql-language\/","title":{"rendered":"How to Create a Stored Procedure in SQL Language"},"content":{"rendered":"<div>\n<p><b>Creating and Using Stored Procedures in the SQL Language<\/b><\/p>\n<p><span style=\"font-weight: 400;\">In this guide, we will learn how to create and use the stored procedures in Transact-SQL and PL\/SQL languages.<\/span><\/p>\n<p><b>A call to a stored procedure that returns a parameter<\/b><\/p>\n<p><span style=\"font-weight: 400;\">We call a system stored procedure that returns a set of characteristics of the database and returns code for the database subject field. The results of the procedure are included in the report:<\/span><span id=\"more-8722\"\/><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">&#13;\nDECLARE @return INT&#13;\nEXECUTE @return = sp_helpdb 'CD'&#13;\nPRINT 'The sp returned: ' + CONVERT(CHAR(10), @return)&#13;\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-8734 size-large\" src=\"https:\/\/assignment.essayshark.com\/blog\/wp-content\/uploads\/2018\/03\/create_stored_proced_1-1024x299.png\" alt=\"\" width=\"604\" height=\"176\"  \/><\/p>\n<p><b>Maintenance of the database by using stored procedures<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Check the database subject area for physical errors using the DBCC utility (DBCC CHECKDB).<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Compress the database, and free up unused blocks by using the system utilities DBCC (DBCC SHRINKDATABASE).<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Update statistics (DB indexes) for all user tables to speed up data selection from the database using the stored procedure sp_updatestats.<\/span><\/li>\n<\/ul>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">&#13;\nDBCC CHECKDB ('CD', REPAIR_REBUILD)&#13;\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-8732 size-large\" src=\"https:\/\/assignment.essayshark.com\/blog\/wp-content\/uploads\/2018\/03\/create_stored_proced_2-1024x351.png\" alt=\"\" width=\"604\" height=\"207\"  \/><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">&#13;\nDBCC SHRINKDATABASE('CD', 10)&#13;\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-8730 size-large\" src=\"https:\/\/assignment.essayshark.com\/blog\/wp-content\/uploads\/2018\/03\/create_stored_proced_3-1024x307.png\" alt=\"\" width=\"604\" height=\"181\"  \/><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">&#13;\nEXEC sp_updatestats&#13;\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-8728 size-large\" src=\"https:\/\/assignment.essayshark.com\/blog\/wp-content\/uploads\/2018\/03\/create_stored_proced_4-1024x335.png\" alt=\"\" width=\"604\" height=\"198\"  \/><\/p>\n<p><b>Creation of the custom stored procedure<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Translate all the names of the dictionary in uppercase using the stored procedure.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">In both options include the contents of the table, sequence of operations, and the ultimate meaning of the table in the original report.<\/span><\/li>\n<\/ul>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">&#13;\nIF EXISTS (SELECT name FROM sysobjects&#13;\n\t\t   WHERE name = 'CD' AND type = 'P')&#13;\n\tDROP PROCEDURE CompUp&#13;\nGO&#13;\n&#13;\nCREATE PROCEDURE CompUp&#13;\nAS&#13;\nUPDATE Clients SET Company = UPPER(Company)&#13;\n&#13;\nEXEC CompUp&#13;\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-8726 \" src=\"https:\/\/assignment.essayshark.com\/blog\/wp-content\/uploads\/2018\/03\/create_stored_proced_5-300x173.png\" alt=\"\" width=\"215\" height=\"124\"  \/><\/p>\n<p><strong>Creation of the custom stored procedures for analyzing database structure<\/strong><\/p>\n<p>Create a stored procedure that prints the lists of the user tables, views, SQL DML triggers and CHECK-constraints, and returns the total number of displayed objects through the parameter using SELECT.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">&#13;\nCREATE PROCEDURE Procedure1&#13;\nAS&#13;\nBEGIN&#13;\n\tSELECT COUNT(type) AS Number, type AS TType FROM sysobjects&#13;\n\tWHERE (type = 'V' OR type = 'U' OR type = 'TR' OR type = 'C')&#13;\n\tGROUP BY type &#13;\nEND&#13;\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-8724 \" src=\"https:\/\/assignment.essayshark.com\/blog\/wp-content\/uploads\/2018\/03\/create_stored_proced_6-300x154.png\" alt=\"\" width=\"237\" height=\"122\"  \/><\/p>\n<p>\u00a0<\/p>\n<blockquote>\n<p><em>As you have already noticed, this guide was written by an expert in IT. It shows how to create a stored procedure in SQL. You can have your assignments done properly if you order them from Assignment.EssayShark.com. Since the range of our experts is vast, it is necessary to select a particular expert for your order. Thus, different experts who work on our team are knowledgeable in different spheres of study. Consider the opportunity to use our service if you want to forget about your homework problems.<\/em><\/p>\n<\/blockquote>\n<blockquote>\n<p><em>We are available 24\/7. After you place an order on our site, our expert will start to work on it immediately. You can contact him or her directly via chat and ask any questions about the order that bother you. Also, you can request free revisions if you don\u2019t like something in the completed order. You can be confident that you will get an assignment that is absolutely correct. What are you waiting for? Place an order and we will help you right now!<\/em><\/p>\n<\/blockquote><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Creating and Using Stored Procedures in the SQL Language In this guide, we will learn how to create and use the stored procedures in Transact-SQL and PL\/SQL languages. A call to a stored procedure that returns a parameter We call <a href=\"https:\/\/www.benedictsol.com\/blogs\/how-to-create-a-stored-procedure-in-sql-language\/\" class=\"read-more\">Read More &#8230;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,25],"tags":[],"class_list":["post-435413","post","type-post","status-publish","format-standard","hentry","category-essay-paper-writing","category-samples"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/435413","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=435413"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/435413\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=435413"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=435413"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=435413"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}