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 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:
DECLARE @return INT EXECUTE @return = sp_helpdb 'CD' PRINT 'The sp returned: ' + CONVERT(CHAR(10), @return)
Maintenance of the database by using stored procedures
- Check the database subject area for physical errors using the DBCC utility (DBCC CHECKDB).
- Compress the database, and free up unused blocks by using the system utilities DBCC (DBCC SHRINKDATABASE).
- Update statistics (DB indexes) for all user tables to speed up data selection from the database using the stored procedure sp_updatestats.
DBCC CHECKDB ('CD', REPAIR_REBUILD)
DBCC SHRINKDATABASE('CD', 10)
Creation of the custom stored procedure
- Translate all the names of the dictionary in uppercase using the stored procedure.
- In both options include the contents of the table, sequence of operations, and the ultimate meaning of the table in the original report.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CD' AND type = 'P') DROP PROCEDURE CompUp GO CREATE PROCEDURE CompUp AS UPDATE Clients SET Company = UPPER(Company) EXEC CompUp
Creation of the custom stored procedures for analyzing database structure
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.
CREATE PROCEDURE Procedure1 AS BEGIN SELECT COUNT(type) AS Number, type AS TType FROM sysobjects WHERE (type = 'V' OR type = 'U' OR type = 'TR' OR type = 'C') GROUP BY type END
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 AssignmentShark.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.
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’t 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!