Learn how to use and apply the user-defined stored procedure along with examining different types of stored procedures in SQL.
The following topics are covered in the tutorial.
You will be using Microsoft SQL server as the database. The concept covered in this tutorial is the same for most of the Relational Database Management System's, but syntaxes are different, although the idea used here serves well for all databases.
Stored Procedure is the block of the SQL statement in Relational Database Management System (RDBMS), and it is typically written by the programmer, Database Administrator, Data Analyst, and is saved and re-used for multiple programs. It can also have different types depending on the various RDBMS. However, the two most crucial Stored Procedures found in any RDBMS are as follows.
Stored Procedure is a prepared compiled code which is stored or cached and then re-used. You can have cached code for re-use which makes maintainability far easier, i.e., it doesn't need to be changed multiple times, which can maintain security.
You will now create a table named
table_Employees as follows.
CREATE TABLE table_Employees ( EmployeeId INT PRIMARY KEY NOT NULL, EmployeeFirstName VARCHAR(25) NOT NULL, EmployeeLastName VARCHAR(25) NOT NULL, EmployeeGender VARCHAR(25) NOT NULL, EmployeeDepartmentID INT )
The table consists of information about employees in a particular company and consists of the following columns:
You can write a query to insert values into the table. The queries to add values to the table is:
INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)
After inserting the values into the table, table_Employees is created.
Stored Procedure let's you write a query once, and the query has a name which can be saved and executed multiple times if needed. By executing the queries, you can have a folder created as Programmabilty->Stored Procedure, and there is the file named dbo.uprocGetEmployees.
The syntax for creating a Stored Procedure is:
CREATE PROCEDURE <<<procedure_name>>> AS BEGIN '''Required SQL Queries''' END
You can create a Stored Procedure by using the SQL statement as follows.
CREATE PROCEDURE procedure_name CREATE PROC procedure_name
Also, you need to create a Stored Procedure with a naming convention other than "sp_.". The Stored Procedure in the example is created with the name:
To execute a Stored procedure, you can use one of three approaches, and run it as follows.
EXEC <> EXECUTE <> <>
In the above program, you can use procedure name uprocGetEmployees and select it to execute the query.
The Stored Procedure can accept single and multiple parameters. You can easily understand single parameters if you first understand multiple parameters.
The syntax for creating a Stored Procedure with multiple parameters is:
CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>> AS BEGIN <<sql_query>> END
The example shows that there is a change in the folder below.
The concept here is very similar to the function in general programming languages like Python, Java, etc. The program above has the function or procedure name called as "uprocGetEmployeesGenderAndDepartment", which has the parameter name @EmployeeGender and @EmployeeDepartmentId which are "called" by passing the required value to our procedure which can be done by one of two ways and it is explained below.
The parameters in this program are @EmployeeGender and @EmployeeDepartmentId which are "called" by passing the value to the parameter.
The value can be specified by one of two ways. You can use either one of them to execute the Stored Procedure with the multiple parameters.
By specifying the parameter names in the query and passing required values:
By the position that matches the parameter of Stored Procedure with your query and pass the required value to the parameter:
You can also modify the Stored Procedure by using the ALTER PROCEDURE command.
ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment @EmployeeGender nvarchar(25), @EmployeeDepartmentId int BEGIN SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId END
Stored Procedures can be quickly deleted by using the following commands:
DROP PROC <> DROP PROCEDURE <>
The lock symbol indicated in the picture below to the left shows that Stored Procedure is encrypted which ensures only authorized people can access it.
You have just completed learning the basics of User Stored Procedures and the topics covered are conceptually similar to any RDBMS.
SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.
This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below.
Understanding various SQL Server system databases and their roles is an excellent aid for managing your SQL Server instance.
The article provides a thorough guide to SQL Server graph databases with their advantages and downsides, cases of use and useful comparisons. Would SQL Server graph database features fit your next project? While you may not know the definitive answer right now, you might be wondering, “What problems does it solve?”.
It is crucial to back up the SQL Server master database daily. Read to know how to restore the master database to the instance.