top of page
Writer's pictureBhavana Adg

SQL Stored Procedures (Part II)

Introduction :

An SQL process is a collection of SQL statements designed to carry out a particular set of instructions. It reduces coding duplication and helps conserve both time and space. The procedure is like a little piece of code that does something specific and is saved in the database. The SQL query will use them as needed to perform the action. Developer-defined procedures are analogous to UDFs in that they perform a specific task. There are two ways to execute a procedure: with the CALL or EXECUTE commands.


The processes can be helpful in the following scenarios:

  • If the process is used by multiple applications, it can be kept on the server and called whenever it is needed. In addition to making the programmed more modular, this will save time by preventing the need to repeat the same steps for each database.

  • Having the process run on the server where it can make the most efficient use of available resources would lessen the need to send and receive data, cutting down on communication expenses.

  • Such procedures are able to verify the complicated constraints which trigger can't handle.

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter [,parameter]) ]

RETURNS <return type>

<local declaration>

<procedure body>


Local and Parameters variables were not required here. This is only brought up while absolutely necessary. Procedure execution is described in the following statement.

call<procedure or function name >(< argument List>);


Stored Procedure Types

A stored procedures as in the SQL Server may often be broken down into two categories:

  • User-defined Stored Procedures

  • System Stored Procedures

User-defined Stored Procedures:

The stored procedures developed via users are called user-defined stored procedures, and they are often developed or administered by database professionals. One and even more SQL statements enabling accessing, modifying, and erasing the data within the database tables are provided by these processes. It is possible to provide both input and output parameters to a user-defined stored process. A user-defined procedure that combines DDL and DML commands. It is possible to further categories this method into two distinct classes:


T-SQL Stored Procedures:

Of the many kinds of SQL Server procedures, Transact-SQL procedures are extremely common. Specified inputs are used to generate outputs. These functions process and return row data in response to INSERT, UPDATE, and DELETE statements, either with or without the parameters.


CLR Stored Procedures:

CLR (Common Language Runtime) and SQL Server procedures have become a collection of SQL commands. Stored procedures developed in a Common Language Runtime (CLR) language, such as Visual Basic for Microsoft Windows (VB.NET) or C#, are the building blocks of a CLR system. The CLR (Common Language Runtime) procedures are .Net objects which execute as in the SQL Server database's RAM.


System Stored Procedures:

Stored procedures in system are primarily responsible for the server's administrative chores. In SQL Server, system procedures are generated during the installation process. Administrators are unable to access or make modifications to systems and databases catalogue tables via direct query or manipulation thanks to the system's stored procedures. Stored processes in the system are commonly overlooked by developers.



Why Do We Need Procedures?

The primary goal of stored procedures in Structured Query Language (SQL) is to speed up database operations like select, update, and delete by hiding the underlying SQL queries from the programmer. Several factors need the use of procedures in SQL:

  • Intentional repetition of SQL statements:

In order to save time and make the programmed easier to maintain, you can create a "stored procedure" to execute the series of the SQL statements repeatedly.

  • Database's ability to encapsulate sophisticated logic:

A single SQL statement may not be adequate to convey the logic contained in a stored procedure, which may include things like loops and conditional statements. This has the potential to improve the code's legibility and comprehension. Errors are less likely to occur as a result.

  • Gains in efficiency for the application:

As stored procedures are already compiled before being placed in the SQL database, they can be executed much faster than dynamic SQL statements, which must be transmitted to a database every time they are used. The functionality of the programmed might be enhanced as a result.

  • Stronger protection for web applications:

Because of the granularity with which permissions for stored procedures may be set, they can be used to restrict access to sensitive information. This can be useful for preventing hackers from accessing the system and stealing sensitive information.

  • Enhanced application maintainability:

With stored procedures, the code can be modularized such that updates to the programme don't have to be made to the entire codebase. This can ease long-term maintenance of the application and lessen the likelihood of introducing new bugs.

  • Modifying the rate of concurrent processing:

By using transactions, which are available for usage with stored procedures, you can ensure that your data remains consistent and reliable even when multiple users are using the database at once.

  • Optimisation:

There will be no impact on the application's use of the data if you alter the database structure. This is helpful if you're developing an app independently of a database and want to make adjustments to the database without affecting the app.




The Advantages of Using Procedures

Some main advantages of using a procedure are as follows:

  • Increased Efficiency

Procedure calls to stored procedures are often quick and efficient since they are built once and then saved in executable form.

This allows for a short response time. The need for RAM is reduced because the code under execution is automatically cached.

  • Improved Productivity

Greater production is the direct outcome of repeatedly reusing the same code.

  • Utilization Simplicity

Developing a stored procedure requires only the use of a Java IDE (IDE). They can then be used on any layer of a network.

  • Extensibility

Scalability is improved by using stored procedures, which separate application execution from the rest of the server.

  • Serviceability

The centralization of scripts on a server makes it much simpler to update an established procedure than it would be to do it on numerous client computers.

  • Protection

If users are only granted permission to access and modify Oracle data via stored procedures, then that data can only be accessed and modified by the definer of those procedures.




The Disadvantages of Using Procedures

The primary disadvantages of using procedures are as follows:

  • Testing

The testing of logic that is hidden away in a stored method can be very challenging. Unless the procedure is actually executed, no data errors will occur while processing stored processes.

  • Debugging

The difficulty or impossibility of debugging stored processes depends heavily on the underlying database technology. There are limited debugging capabilities in some relational databases, such as Microsoft SQL Server.

  • Versioning

The saved procedure doesn't work with version control.

  • Cost

To gain access to the SQL and create a more effective stored procedure, you'll need a DBA, who is essentially an additional developer. There will be an increased expense because of this.

  • Portability

There is no guarantee that complex stored processes will transfer to newer versions of an identical database. In the specific scenario of changing from Oracle to another database system, this is especially true (MS SQL Server).


Example for SQL Procedure

The following is an example of the code for SQL procedure that includes in the customer ID and first name as input. Now whenever we want to fetch all customers who live in the USA .






  • Executing Stored Procedure

Now whenever we want to fetch all customers who live in the USA, we can simply call the procedure mentioned above. For example ,

CALL us_customers();



  • Parameterized Procedure

We can pass our own value to stored procedures so the same SQL command works differently for different data value. Suppose we want to fetch records where the value is USA in the country column. So we'll write SQL queries as,

SELECT * FROM Customers

WHERE country = 'USA';



And again if we want to fetch records where the value is UK in the country column, we'll write out SQL queries as,


SELECT * FROM Customers

WHERE country = 'UK;


The only difference between the two instances above is the value to look up in the country column.


Hence, we can create a stored procedure and call it with different values rather than having to repeat the same code. For instance,


CREATE PROCEDURE ctr_customers (ctr VARCHAR(50))

LANGUAGE SQL

AS $$

SELECT customer_id, first_name

FROM Customers

WHERE Country = ctr;

$$;


The parameter ctr is required in this case when calling the stored method.


For instance,


  • Calling the stored procedure with 'USA' as parameter value

CALL ctr_customers ('USA');


  • Calling the same stored procedure again with another parameter value 'UK'

CALL ctr_customers ('UK');

  • Drop Procedure

We can delete stored procedures by using the DROP PROCEDURE command.

For example,

DROP PROCEDURE us_customers;


Conclusion :


When a predetermined condition is met in a database server, a stored procedure known as a trigger is activated and runs. While triggers can be helpful, they are typically avoided when possible because of the complexity they add to the underlying data. There is a suitable approach that can be used to substitute triggers on occasion. In SQL, triggers are essential because they facilitate code reuse and reduce computation costs by accelerating response times. In conclusion, the use of stored procedures in SQL improves not only the likelihood of code reuse but also the overall efficiency of the database. How? Because less data is being transmitted across the network, network traffic is being reduced. While memory usage increases, the seek speed decreases, and code reuse is helpful, the overall cost is minimal. Stored procedures can be written with or without variables, and they can be called with or without providing the parameters they were given.




86 views

Recent Posts

See All
bottom of page