Microsoft SQL Server Certification Course
- 5k Enrolled Learners
- Weekend
- Live Class
Procedures are the subprograms which can be created and saved in the database as database objects. Just as you can in other languages, you can create and drop procedures in SQL as well. In this article, let’s explore procedures in SQL with syntax and examples.
The topics discussed in the article are:
A procedure in SQL (often referred to as stored procedure), is a reusable unit that encapsulates the specific business logic of the application. A SQL procedure is a group of SQL statements and logic, compiled and stored together to perform a specific task.
Listed below are key features of the SQL procedures:
Now that you know what procedures are and why they are needed, let’s discuss the syntax and example of procedure in SQL.
The following illustrates the basic syntax of creating a procedure in SQL:
CREATE [ OR REPLACE] PROCEDURE procedure_name [ (parameter_name [IN | OUT | IN OUT] type [ ])] {IS | AS } BEGIN [declaration_section] executable_section //SQL statement used in the stored procedure END GO
Parameter
A parameter is a variable that holds a value of any valid SQL datatype through which the subprogram can exchange the values with the main code. In other words, parameters are used to pass values to the procedure. There are 3 different types of parameters, which are as follows:
Other Terminologies
Master the art of database querying and data retrieval with our SQL Certification Course.
Find out our MS SQL Course in Top Cities
India | India |
SQL Training in Bangalore | SQL Course in Pune |
SQL Training in Chennai | SQL Course in Mumbai |
SQL Training in Hyderabad | SQL Course in Kolkata |
Procedure in SQL: Examples
The following example creates a simple procedure that displays the welcome message on the screen when executed. Then, the procedure will be:
CREATE OR REPLACE PROCEDURE welcome_msg (para1_name IN VARCHAR2) IS BEGIN dbms_output.put_line (‘Hello World! '|| para1_name); END; /
Execute the stored procedure. A standalone procedure can be called in two ways −
The above procedure can be called using Execute keyword as follows:
EXEC welcome_msg (‘Welcome to Edureka!’);
Output
Hello World! Welcome to Edureka
Procedure is executed, and the message is printed out as “Hello World! Welcome to Edureka”.
Let’s suppose that you have a table with Emplyoee details, like, EmployeId, Firstname, Lastname, and DepartmentDetails.
This example creates a SQL procedure that will return an employee name when the EmployeId is given as the input parameter to the stored procedure. Then, the procedure will be:
Create PROCEDURE GetStudentName ( @employeeID INT, --Input parameter , employeID of the employee @employeName VARCHAR(50) OUT --Output parameter, employeeName of employee AS BEGIN SELECT @employeName= Firstname+' '+Lastname FROM Employee_Table WHERE EmployeId=@employeID END
Steps to execute:
The above procedure on giving employee id as input returns the name of that particular employee. Suppose if we have an output parameter then we first need to declare the variable to collect the output values. Now let’s check out the advantages of procedure in SQL.
The main purpose of stored procedures in SQL is to hide direct SQL queries from the code and improve the performance of database operations such as select, update, and delete data. Other advantages of procedure in SQL are:
To conclude, procedures in SQL (stored procedures) not only enhance the possibility of reusing the code but also increases the performance of the database. How? By reducing the traffic of the network by reducing the amount of information sent over the network. With this, we have come to the end of this article.
If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of this ‘Procedures in SQL; article and we will get back to you.
Course Name | Date | Details |
---|---|---|
Microsoft SQL Server Certification Course | Class Starts on 12th October,2024 12th October SAT&SUN (Weekend Batch) | View Details |
Microsoft SQL Server Certification Course | Class Starts on 14th December,2024 14th December SAT&SUN (Weekend Batch) | View Details |
edureka.co
This is an interesting resource and something that I can add for me to learn SQL. Thank you.