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 INSERT
, SELECT
, UPDATE
, DELETE
, WHERE
, etc.
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_nameASSQL_statementsGO;
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.
EXEC SQL_procedure_name;
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 INTOinformation(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_infoASBEGINSELECTName,GenderFROMinformationEND;
EXEC show_info
The following result will be displayed.
Name | Gender |
---|---|
George | Male |
Emma | Female |
Harry | Male |
Ava | Female |
Olivia | Female |
Thomas | Male |
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)ASSELECT * FROM information WHERE Gender= @genderGO;
EXEC showFemale @gender= 'Female'
The output of the code above will be as follows.
Name | Age | Gender | City |
---|---|---|---|
Emma | 22 | Female | Manchester |
Ava | 17 | Female | Manchester |
Olivia | 25 | Female | Manchester |
We can also create stored procedures with multiple parameters, as shown below.
CREATE PROCEDURE filter @gender nvarchar(30) @age nvarchar(10)ASSELECT * FROM information WHERE Gender= @gender AND Age >= @ageGO;
EXEC filter @gender='Male', @age = 22
The code above will display all the male entries with an age greater than or equal to 22.