SQL | Stored Procedure
A SQL Server Stored Procedure is a database object which contains pre-compiled queries (a group of T-SQL Statements), designed to perform a task whenever we called.
By default, a stored procedure compiles when it gets executed for the first time. It also creates an execution plan that is reused for subsequent executions for faster performance.
SQL Server stored procedures are used to group one or more Transact-SQL statements into logical units. The stored procedure is stored as a named object in the SQL Server Database Server.
When you call a stored procedure for the first time, SQL Server creates an execution plan and stores it in the cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan to execute the stored procedure very fast with reliable performance.We can invoke the procedures by using triggers, other procedures, and applications such as C#, Asp.Net Java, Python, PHP, etc.
Create a Stored Procedure in SQL Server
you can create a stored procedure by using the CREATE PROCEDURE or CREATE PROC statement.
You can create a procedure with or without parameters.
View the text of a Stored Procedure in SQL Server
Once you created the stored procedure and later if you want to view the text of the stored procedure then you need to use the sp_helptext system-defined stored procedure by supplying the procedure name as a parameter
Drop a Stored Procedure
Benefits of using a stored procedure
It can be easily modified: We can easily modify the code inside the stored procedure without the need to restart or deploying the application. So, when we want to change the logic inside the procedure we can just do it by simple ALTER PROCEDURE statement.
Reduced network traffic: When we use stored procedures instead of writing T-SQL queries at the application level, only the procedure name is passed over the network instead of the whole T-SQL code.
Reusable: Stored procedures can be executed by multiple users or multiple client applications without the need of writing the code again.
Security: Stored procedures reduce the threat by eliminating direct access to the tables. we can also encrypt the stored procedures while creating them so that source code inside the stored procedure is not visible. Use third-party tools like ApexSQL Decrypt to decrypt the encrypted stored procedures.
Performance: The SQL Server stored procedure when executed for the first time creates a plan and stores it in the buffer pool so that the plan can be reused when it executes next time.
Create a Stored Procedures without parameters
Execute Stored Procedures
use 'EXEC ProcedureName' to execute stored procedures.
Creating a stored procedure with parameters
Execute Stored Procedures
While executing the stored procedure we need to pass the input parameter.
OR
Right-click on the stored procedure you want to execute and choose the Execute Stored Procedure
The Execute Procedure window will appear. If the procedure has any parameters, we must assign/pass them before clicking OK to execute it. If no parameters are defined, click OK to run the procedure.
We can Also created Stored Procedure by Using SSMS UI
Select the Database -> Programmability -> Stored Procedures.
Right-click on the Stored Procedures folder to open the menu and then select the New -> Stored Procedure option
When we select the New Stored Procedure option, we will get the new query window containing the default Stored Procedure Template. Here, we can add the procedure name, parameters (if any), and the SQL query we want to use.
View All Available/Created Stored Procedures
Open SQL SSMS
Navigate to the Programmability -> Stored Procedures.
Next, select the Stored Procedure menu and expand it. You will see the available stored procedures.
Rename stored procedures
SQL Server does not allow us to change the name of a stored procedure. Because renaming a stored procedure does not modify the name of the corresponding object in the sys.sql_modules. Therefore, if we need to change the existing stored procedure, simply DROP and recreate it with a new name.
Modify Stored Procedures
Open Stored Procedures In Modify/Alter Mode
1. Using T-SQL Query
2. Using SQL Server Management Studio
List all stored procedures in SQL Server
