Structured Query Language (SQL) is a language that is used to access and manipulate databases. With the help of SQL queries, we can store data in the database in an accessible form, easily retrieve it, and update it whenever needed.
Some basic and common SQL commands are
Similarly to functions that we make so that code can be used again, we have stored procedures in SQL.
In SQL, a stored procedure is a prepared SQL code that developers can save so that the code can be easily reused later.
If we have SQL code that needs to be used many times, we can store it as stored procedures and call it whenever necessary. We can also pass parameters to the stored procedures, just like in functions, to make it more customizable.
CREATE PROCEDURE SQL_procedure_name AS SQL_statements GO;
The code above shows us how to create a stored procedure. To create a stored procedure, you can use the
CREATE PROCEDURE statement followed by the stored procedure name. Then, after the
AS statement, we write all the SQL queries/code that needs to be reused and conclude the stored procedure with the
GO statement. Below is the code to call/execute the stored procedure.
Consider the following database named information.
CREATE TABLE information ( -- <Variable name> <Variable type> Name varchar(255), Age varchar(255), Gender varchar(255), City varchar(255) ); INSERT INTO information (Name, Age, Gender , City) VALUES -- <Variable values> ('George','20', 'Male', 'London'), ('Emma','22', 'Female', 'Manchester'), ('Harry','15', 'Male', 'Cambridge'), ('Ava','17', 'Female', 'Manchester'), ('Olivia','25', 'Female', 'Manchester'), ('Thomas','33', 'Male', 'Cambridge'); SELECT * FROM information
Now, let’s create a stored procedure with the code to select all entries from the database above.
CREATE PROCEDURE show_info AS BEGIN SELECT Name, Gender FROM information END;
The following result will be displayed.
As mentioned earlier, we can also pass parameters in stored procedures.
Consider an example of a stored procedure with a single parameter.
CREATE PROCEDURE showFemale @gender nvarchar(30) AS SELECT * FROM information WHERE Gender= @gender GO;
EXEC showFemale @gender= 'Female'
The output of the code above will be as follows.
We can also create stored procedures with multiple parameters, as shown below.
CREATE PROCEDURE filter @gender nvarchar(30) @age nvarchar(10) AS SELECT * FROM information WHERE Gender= @gender AND Age >= @age GO;
EXEC filter @gender='Male', @age = 22
The code above will display all the male entries with an age greater than or equal to 22.
View all Courses