SQL Stored Procedure

The SQL stored procedure is a set of one or more pre-compiled SQL statements stored in the database as a group of logical units. Also, we can define the procedure as its SQL code saved in the database, so that it can be reused depending upon requirements.

It can be used in various business applications where the frequent database operations such as insert, update, delete, complex queries, and reports are involved. Each stored procedure in SQL always contains a procedure name, parameter lists (input/output), exception handling, and Transact-SQL statements.SQL stored procedure is saved as a named object in the database.

We can invoke or execute stored procedures using triggers, and execute the stored procedure in different stored procedures and by applications such as Java, PHP, etc.

In the SQL server when we execute the procedure for the first time, its execution plan is stored in the cache memory. This execution plan is used by the SQL server in a subsequent execution of that particular procedure and allows for quick and efficient data retrieval.

SQL Stored Procedure

 

Store Procedure Features

  • Reduces Traffic: As the procedure is precompiled and saved in the database, it helps to reduce network traffic between the user applications and the database servers, resulting in increased performance of the database.
  • Stronge Security: The procedure enforces security rules to prevent unauthorized access to critical and important data.
  • Reuse of code: The procedure code can be reused across different applications.
  • Improved performance: The procedure is precompiled and saved in a database which helps to execute SQL statements sent by user applications.

Stored procedure syntax/create procedure syntax

Below is the syntax to create a procedure in SQL –

CREATE PROCEDURE procedurename
   @parameter1 datatype,
   @parameter2 datatype,
 @parameter3 datatype,
 @parameter4 datatype

AS
BEGIN
   -- SQL statement to be executed
END

In the above syntax CREATE PROCEDURE statement is used to define procedure creation with the given procedure name along with the parameters. The parameters always start with @ symbol. As keyword begins procedure definition. The SQL statements are within begin and end blocks of the stored procedure.

example

CREATE PROCEDURE dbop.Getproductdetails
   @storeno INT
AS
BEGIN
   select * from product
   WHERE storeid= @storeno
END

a stored procedure can be executed as follows. while executing stored procedures we can use execute or exec words for defining stored procedure execution.

 exec dbop.Getproductdetails  storeid= '2';
or
 exec dbop.Getproductdetails '2';

or
 execute dbop.Getproductdetails  storeid= '2';
or
 execute dbop.Getproductdetails '2';

Types of Procedure Parameters

  1. Input parameters: The input parameters are used to pass input values from the user to the procedure.
  2. Table-valued parameters: It is used to pass a table variable as a parameter to a procedure.
  3. Default parameters: The default parameters specify a default value that will be used if no value is provided for the parameter.
  4. Cursor parameters: The Cursor parameter is used to pass a cursor to a procedure.
  5. Output XML parameters: The Output XML parameter return XML data from a procedure.
  6. Output parameters: The output parameters are used to return output values from the procedure to the user.
  7. Input/Output parameters: Sometimes, Input/Output parameters are allowed in a procedure to accept input values and return output values.

example in-out parameters

CREATE PROCEDURE dbop.Getproductsalesdetails
   @storeno INT,@totalsales bigint output
AS
BEGIN
   select sum(sales) as totalsales from product
   WHERE storeid= @storeno
END

exec dbop.Getproductsalesdetails '2','0'

In above example will take @storeno as the input parameter and @totalsales as the output parameter.

Rename procedure

We can rename procedures using sp_rename built-in stored procedure

syntax

sp_rename 'oldprocedurename', 'newprocedurename';

 

example

sp_rename 'dbop.Getproductsalesdetails', 'Getstoreproductsalesdetails';

Modify  Procedure

We can modify procedures using Alter procedure statement. We can add or remove parameters and do logic changes using Alter procedure statement.

Syntax
Following is the basic syntax to modify a stored procedure in SQL −

ALTER PROCEDURE dbop.Getproductdetails
   @storeno INT,@storaddress varchar(500)
AS
BEGIN
   select * from product
   WHERE storeid= @storeno and storedaddress=@storaddress
END

DROP/DELETE Procedure

We can drop procedures using the DROP Procedure statement. When we drop the procedure it permanently drops from the database. The recovery of the stored procedure is not possible until and unless we have a backup.

Syntax
Following is the basic syntax to delete a stored procedure in SQL −

DROP PROCEDURE  procedurename;

 

example

DROP PROCEDURE  dbop.Getproductdetails;