The Stored Procedures save a collection of one or a group of statements saved in the SQL Server. Here, we explain how to create, rename, alter, and delete Stored Procedures in SQL Server with examples. The following are the benefits of using them.
- All the SQL Server Stored Procedures are pre-compiled, and their execution plan is cached. So, when you execute the same one again, it will use the cache.
- It will help you to encapsulate the business rules and policies. For example, the database admin will create a procedure, and multiple users will access it from JAVA, C#, Python, R, etc.
- Instead of sending hundreds of lines of code, it is better to use a stored procedure so that we can call the single statement (its name) rather than writing the complex statement or sending it over the network.
- The SQL Server stored procedures help you to access the database objects in a more secure and uniform way
- Network bandwidth conservation (protect from harm or delay)
I suggest you refer to the following examples to understand the SQL Stored procedures in detail:
- SELECT to understand, How to write the SELECT Statements inside it
- INSERT article to follow the steps in writing the INSERT Statements inside it.
- UPDATE to write the UPDATE Statements inside it.
- Input Parameters to declare and use the input Parameter, including single or multiple parameter values.
- Output Parameters: The output parameters help output the values.
- Return Values
- Temporary Tables: Create temporary procedures that include the local temporary procedure or global temporary procedure.
- Some of the useful System Stored Procedures.
Before we get into the practical example, let us see the syntax.
SQL Server Stored Procedure Syntax
The basic syntax of SQL Server stored procedure is as shown below:
CREATE [OR ALTER] PROCEDURE [Schema_Name].Procedure_Name
@Parameter_Name Data_type,
....
@Parameter_Name Data_type
AS
BEGIN
-- Query
-- SELECT, Insert, Update, Or Delete Statements
-- You can Use CTE also
END
But, first, let me explain the syntax.
- Schema_name: Please specify the schema name. For example, dbo, or Humanresource, etc.
- Procedure_Name: You can specify any SQL stored procedure name you wish to give other than the system reserved keywords. Please try to use meaningful names so that you can identify them quickly.
- @Parameter_Name: Every sp accepts zero or more parameters, depending on user requirements. While declaring the parameters, don’t forget the appropriate data type. For example (@FullName VARCHAR(50), @Age INT)
Create Stored Procedures in SQL Server
I forgot that we could create them two ways—the data we use for this demonstration.
Create Stored Procedure using SQL Server Management Studio
Before we start creating any, let us see whether our database has any existing ones. To view the existing ones, select Database -> Programmability -> SP. From the below screenshot, you can observe that our database has none.
Right-click on the SQL Stored Procedures folder will open the context menu. Please select the New -> Stored Procedure..option from it.
Once you click on the New option, it will open the new Query window with the default Template.
Here, you can add the name, Parameters (if required), and the transact query you want to use.
Create SQL Stored Procedures using Query
This example shows how to create SP using the Create Statement.
CREATE PROCEDURE SelectingAllRecordsInEmployee
AS
BEGIN
SET NOCOUNT ON;
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [Employee]
END
GO
Output
Messages
--------
Command(s) completed successfully.As you can see, the command has been completed successfully. The following screenshot shows the simple sp that we created earlier.
Execute Stored Procedure in SQL Server
We can execute sp in two ways:
Execute using Management Studio
To execute the Stored Procedure using the SQL Server Management Studio (SSMS), Please navigate to Programmability -> SP. Next, select the one you want to execute (SelectingAllRecordsInEmployee), and right-click on it will open the context menu. Then, please, choose the Execute option.
Once you select the Execute option, an Execute Stored Procedures window will open in SQL Server Management Studio. We must assign/pass those values and click OK to execute if it has any parameters. Our sp does not have any parameters here, so click OK to run.
Once you click on the OK button, a new query window opens with the following auto-generated code.
DECLARE @return_value int EXEC @return_value = [dbo].[SelectingAllRecordsInEmployee] SELECT 'Return Value' = @return_value GO
Execute SP
Execute Stored Procedure using the SQL Server EXEC Command (Execute Command)
EXEC [dbo].[SelectingAllRecordsInEmployee]
Rename Stored Procedure in SQL Server
There is no such thing called renaming the SQL Stored procedure. Because renaming it will not change its corresponding object name within the sys.sql_modules. So, if you want to rename any existing sp, then DROP and recreate it with a new name.
-- Renaming SP
-- Check whether there is a SP with the Following Name
IF OBJECT_ID ( 'SelectingAllRecordsInEmployee', 'P' ) IS NOT NULL
-- If so, Drop that Procedure
DROP PROCEDURE SelectingAllRecordsInEmployee;
GO
-- Creating SP with New name
CREATE PROCEDURE SelectingEmployeeRecords
AS
BEGIN
SET NOCOUNT ON;
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [Employee]
END
GO
output
Messages
-------
Command(s) completed successfully.From the screenshot below, we successfully deleted the old one and created a completely new one.
Modify Stored Procedures in SQL Server
The following examples help you learn the steps to change them using the Management Studio (SSMS) and query.
Change using SSMS
To alter stored procedure using the SQL Server Management Studio, right-click on the name (SelectingEmployeeRecords) that you want to change, and select the Modify option.
Once you choose the option, a new query window opens with an auto-generated ALTER PROCEDURE code. You can change it as per your requirement.
Use Alter to Modify Stored Procedure in SQL Server
This example will show you the steps involved in modifying the existing one using the ALTER PROCEDURE statement. To do so, Click the New query and return the following query.
ALTER PROCEDURE [dbo].[SelectingEmployeeRecords]
AS
BEGIN
SET NOCOUNT ON;
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation] AS Profession
,[YearlyIncome]
,[Sales]
FROM [Employee]
END
Run the above alter SP query
Messages
--------
Command(s) completed successfully.Let us execute the above to check whether we successfully changed or not.
EXEC [dbo].[SelectingEmployeeRecords] GO
Delete Stored Procedure in SQL Server
The following examples will show you, How to delete or drop the Stored Procedures using the Transact Query and Management Studio (SSMS).
Delete using Management Studio
In this example, we will show how to delete it using the SSMS or Management Studio. To do so, Please navigate to the one you want to delete. Next, right-click on the name to open the context menu. Finally, you can click on the Delete option, as shown below.
For this demonstration, we are going to delete SelectingEmployeerecords. Once you select the delete option, a Delete Object window will open, as shown below. Next, click the Show Dependencies button to check the dependencies, and then click OK to delete them.
Delete or Drop
This example shows you how to drop or delete using a query.
IF OBJECT_ID ( 'SelectingEmployeeRecords', 'P' ) IS NOT NULL
DROP PROCEDURE SelectingEmployeeRecords;
GO
TIP: It is good practice to check whether the name or sp exists in the database or not using IF OBJECT_ID IS NOT NULL
SQL Stored Procedures Best Practices
The following is a list of suggestions that might help you to improve the performance of SQL Server stored procedures.
- Try using the Schema Names while creating or referencing any database object. It will decrease the database engine processing time.
- Always specify the required column names within the SELECT Statement. And avoid the SELECT * Statement.
- While Creating or altering a table using (CREATE TABLE or ALTER TABLE), use the DEFAULT keyword to assign the default values to the Columns. It will prevent the NULL values and assign those default values to the column data.
- When creating Temporary Tables inside the sp, You have to specify whether the Column accepts NULLS or NOT explicitly. It can be done by using NULL or NOT NULL.
- Rather than Extracting, or Inserting a large amount of data, try to work with less essential data. It reduces the query processing load and increases the query performance.
- Use the SET NOCOUNT ON statement within the SQL server stored procedure. It will turn off the messages that the SQL Server sends to the Client. It includes the number of rows updated, deleted, etc.
- Try to replace the UNION Operator or OR Operator with the UNION ALL Operator unless you are looking for distinct values.
- If possible, avoid using the SCALAR Functions in the SELECT statement that returns a large amount of data. This is because the scalar function applies on each row (row basic), affecting the query performance.
- To handle the errors, SQL allows us to use the TRY CATCH feature inside the Stored Procedures. So, try to use the TRY CATCH feature.
- Always use the BEGIN..COMMIT TRANSACTION within the SP. Remember that the transaction should be as short as possible. Otherwise, there is a danger of either deadlock or longer locking.
Best Approach to creating Stored Procedures in SQL
It will be an ideal way to create in real-time
IF OBJECT_ID ( 'SelectingEmployeeRecords', 'P' ) IS NOT NULL
DROP PROCEDURE SelectingEmployeeRecords;
GO
CREATE PROCEDURE SelectingEmployeeRecords
AS
BEGIN
SET NOCOUNT ON;
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [Employee]
END
GO
SQL Encrypted Stored Procedure
We can create the encrypted Stored Procedure by adding the WITH ENCRYPTION keyword. For example, if we add the below line in the above query.
CREATE PROCEDURE SelectingEmployeeRecords WITH ENCRYPTION AS BEGIN .... ........
Comments are closed.