What is the SQL Procedure?

A pre-compiled stored procedure is a logical unit made up of one/more SQL statements (SQL stands for Structured Query Language). The database server saves it as an object. An SQL procedure is a collection of SQL statements and logic that is written and saved to accomplish a specified goal. An SQL procedure consists of a name, a parameter list, and a set of Transact-SQL statements. Stored procedures are stored as named objects in SQL Database Server.

Features of SQL procedure

• They are simple to implement since they are written in a high-level, strongly-typed language.
• Different sorts of parameters are supported namely input, output and input-output.
• Exterior procedures are less dependable than internal procedures.
• Reliability and maintainability are aided via SQL operations.
• Supports effective condition and error-handling.
• Return a status value to a calling function to indicate success/failure and the reason for failure.

Advantages of SQL procedure

Reduced network traffic: A stored procedure decreases network traffic between the app and the database server. Rather than sending separate queries which involves execution of multiple SQL statements, the application just needs to convey the name of the stored procedure and its parameters.

Stronger security: Procedure is safe because it controls which procedures and actions a user can execute. It allows access control or permissions to be granted at the database object level to improve security.

Reusable: Procedures can be reused after they have been saved. It reduces inconsistencies in the code, avoids duplicate rewrites and makes the code visible to all apps and users.

Simple to maintain: The procedures are easier to maintain rather than executing individual SQL statements repeatedly.

Different types of stored procedures

• User-defined procedures.
• System stored procedures.

User-defined Procedures

Database developers or database administrators construct user-defined stored procedures. These procedures contain one or more SQL statements to execute queries for selecting, updating, or deleting data from database tables. A stored SQL server procedure with user-defined parameters accepts input parameters and returns output parameters. DDL (Data Definition Language commands - CREATE, DELETE, DROP and so forth) and DML statements (Data Manipulation Language commands - INSERT, UPDATE, DELETE and so forth) can be used in a user-defined procedure.

System stored procedure

The administrative tasks of the server are mostly dependent on system stored routines. SQL Server creates system procedures when it is installed. System stored procedures helps in administrative tasks. System stored SQL server procedures are frequently overlooked by developers. For example, the stored procedure sp_help can be used to obtain details about various database objects such as tables, views and so forth.

SQL Server Stored Procedure (Transact-SQL) and their functions

1. sp_adduser - adds a new user to the database.
2. sp_changedbowner - changes the owner of the current database.
3. sp_droprole - removes a database role from the current database.
4. sp_dropuser - removes a database user from the current database.
5. sp_password - adds or modifies the password associated with an account.

SQL procedures syntax

The following illustrates the basic syntax of creating a stored procedure in SQL:

CREATE [ OR REPLACE] PROCEDURE procedure_name [

(parameter_name [IN | OUT | IN OUT] type [ ])]

{IS | AS }

BEGIN [declaration_section]

executable_section 

END

GO

Terminologies used in the syntax

Parameter

The following are the three different types of parameters:

IN: This is the default parameter, which accepts values from the caller application at all times. It is a read-only variable in the subprograms and its value can't be altered within the procedure.

OUT: This variable is used to get output from the subprograms.

IN OUT: This parameter is used to provide input to the subprograms as well as to receive output from them.

Example

When the following example procedure is executed, it displays a "Hii" message on the screen.

create or replace procedure message

as

begin

dbms_output.put_line (‘Hii');

end; 

Procedure created.

Use the 'execute' key to display the procedure that is been created.

execute message;

Output: Hii

Put the stored procedure to use. There are two ways to invoke a standalone procedure.

• Using the keyword 'execute'.
• Inside a SQL block.

Limitations of SQL procedure

• Debugging stored procedures is never easy. It is not recommended to write and execute complicated business logic using them.
• Testing: Data related errors in stored procedures does not show up until runtime. It is difficult to test the logic used within stored procedure since it remains encapsulated.
• Expensive: In terms of DBAs, stored procedures are expensive to manage because firms must pay more for specialized DBAs. A DBA is far more equipped to manage sophisticated database operations.
• Vendor-specific: Stored procedures built on one platform will not operate on another. The procedure written for SQL Server does not work with Oracle since Oracle procedures are more sophisticated.

Applications

A stored procedure groups SQL statements together so that they can be executed with a single call. This decreases network traffic and improves round-trip response time.

Context and Applications

This topic is important for postgraduate and undergraduate courses, particularly for, 

• Bachelors in Computer Science Engineering.
• Associate of Science in Computer Science.

Practice Problems

Question 1: In an SQL procedure, which of the following type of parameter is used to store the value of input argument and final result?

1. Put and Get
2. Get and Put
3. In and Out
4. None of these

Answer: Option C is correct.

Explanation: IN parameter is used to store the input arguments and the OUT parameter to store and display the final result.

Question 2: What is the format of a compound statement in the procedure SQL?

1. Begin ……. end
2. Begin atomic……. end
3. Both Begin ……. end and Begin atomic……. end
4. None of these

Answer: Option C is correct.

Explanation: SQL statements are enclosed between BEGIN and END statements form a compound SQL statement. Begin atomic... end can also be used.

Question 3: Transact-SQL procedure can use ____________ datatype for OUTPUT parameter.

1. Cursor
2. Float
3. Integer
4. Double

Answer: Option A is correct.

Explanation: Output parameter in case of Transact SQL must be a cursor type.

Question 4: _________ is a system procedure.

1. sp_changeowner
2. vi_changeowner
3. cp_changeowner
4. None of these

Answer: Option A is correct.

Explanation: The owner of a database object can be changed with sp_changeowner.

Question 5: ________ stored procedure returns details of any database object.

1. sp_changeowner
2. sp_owner
3. sp_change
4. None of these

Answer:  Option D is correct.

Explanation: The answer is none of these. sp_help provides details of database objects such as tables, views and so forth.