How to Create a Stored Procedure in SQL Language

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)

EXEC sp_updatestats

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

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!