A Story of the Stored Procedure

In this super-optimised tech world, writing a same block of code too many times is a mundane task and a bad practice. That’s where code reusability needs to be shown up.

In Front end JS frameworks, we create common components and inject them in the other component(s) wherever needed. Developing a reusable Header is the best example of such a common component.

Likewise, from backend developer’s perspective, when it comes to API development, we can choose to go with a mechanism which would provoke code reusability principle. In this case, Stored Procedure is the way out !

To begin with, stored procedure is nothing but the prepared sql statement(query) that can be saved and reused over and over again by executing them.

🌈 Stored procedures are the easiest when it comes to Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

It gets executed as,

EXEC procedure_name;

Can we use it with Parameters ? 🤔

Yes, Stored procedures can be written using either a single parameter or multiple parameters. Example given below specify their usage.

⚡️ Procedure with One Parameter

The stored procedure to select Students from a particular City from the “Students” table is given below:

CREATE PROCEDURE SelectAllStudents @City varchar(30)
AS
SELECT * FROM Students WHERE City = @City
GO;

Below is how to execute above stored procedure,

EXEC SelectAllStudents @City = ‘London’;

⚡️ Stored Procedure with Multiple Parameters

Setting up multiple parameters is no rocket science. It’s as easy as to make a list of each parameter and the data type separated by a comma.

The following stored procedure selects Students from a particular City with a particular PostalCode from the “Students” table:

CREATE PROCEDURE SelectAllStudents @City varchar(30),@PostalCode varchar(10)
AS
SELECT * FROM Students WHERE City = @City AND PostalCode = @PostalCode
GO;

Executing it is simple as given below,

EXEC SelectAllStudents @City = ‘London’, @PostalCode = ‘WA1 1DP’;

Using Stored procedure makes life of a backend developer easier as it plays a crucial role in avoiding code redundancy which also leads to better memory enhancement.

Happy executing 😎