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:
[code language=”sql”]
DECLARE @return INT
EXECUTE @return = sp_helpdb ‘CD’
PRINT ‘The sp returned: ‘ + CONVERT(CHAR(10), @return)
[/code]
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.
[code language=”sql”]
DBCC CHECKDB (‘CD’, REPAIR_REBUILD)
[/code]
[code language=”sql”]
DBCC SHRINKDATABASE(‘CD’, 10)
[/code]
[code language=”sql”]
EXEC sp_updatestats
[/code]
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.
[code language=”sql”]
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
[/code]
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.
[code language=”sql”]
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
[/code]
SQL Homework Assistance from AssignmentShark Experts
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 SQL homework. 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? Get online assignment help and we will assist you right now!