In this article I will explain with an example, how to perform Select, Insert, Update and Delete operations using a single Stored Procedure in SQL Server.
Performing Select, Insert, Update and Delete operations using a single Stored Procedure is supported in SQL Server versions i.e. 2005, 2008, 2008R2, 2012, 2014 or higher.
Database
I have made use of the following table Customers with the schema as follows.
![Single Stored Procedure for Select Insert Update Delete in SQL Server]()
I have already inserted few records in the table.
![Single Stored Procedure for Select Insert Update Delete in SQL Server]()
Note: You can download the database table SQL by clicking the download link below.
Stored Procedure for Select, Insert, Update and Delete
The following stored procedure will be used to perform Select, Insert, Update and Delete operations on the Customers table of the SQL Server database.
This Stored Procedure accepts has the first parameter named @Action which informs the Stored Procedure about the operation i.e. Select, Insert, Update and Delete needs to be performed.
The Stored Procedure performs multiple operations on the same Table and hence the other parameters are specified with default NULL values in order to make the Stored Procedure work without specifying all parameter values.
CREATE PROCEDURE [dbo].[Customers_CRUD]
@Action VARCHAR(10)
,@CustomerId INT = NULL
,@Name VARCHAR(100) = NULL
,@Country VARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF @Action = 'SELECT'
BEGIN
SELECT CustomerId, Name, Country
FROM Customers
END
--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO Customers(Name, Country)
VALUES (@Name, @Country)
END
--UPDATE
IF @Action = 'UPDATE'
BEGIN
UPDATE Customers
SET Name = @Name, Country = @Country
WHERE CustomerId = @CustomerId
END
--DELETE
IF @Action = 'DELETE'
BEGIN
DELETE FROM Customers
WHERE CustomerId = @CustomerId
END
END
Executing the Stored Procedure
The Stored Procedure will now be executed to perform different operations i.e. Select, Insert, Update and Delete on the Customers table.
SELECT Operation
When performing SELECT operation, the value of the @Action parameter is passed as SELECT while the other parameters are not supplied with values which sets their default values as NULL.
EXEC [dbo].[Customers_CRUD]@Action = 'SELECT'
![Single Stored Procedure for Select Insert Update Delete in SQL Server]()
INSERT Operation
When performing INSERT operation, the value of the @Action parameter is passed as INSERT and the values of @Name and @Country parameters are supplied. The @CustomerId parameter value is not supplied as the CustomerId field is set to Identity (Auto Increment) as true.
EXEC [dbo].[Customers_CRUD]@Action = 'INSERT'
,@Name = 'Shen Ching'
,@Country = 'China'
![Single Stored Procedure for Select Insert Update Delete in SQL Server]()
UPDATE Operation
When performing UPDATE operation, the value of the @Action parameter is passed as UPDATE and the values of @CustomerId, @Name and @Country parameters are supplied.
EXEC [dbo].[Customers_CRUD]@Action = 'UPDATE'
,@CustomerId = 5
,@Name = 'Max Haynes'
,@Country = 'Australia'
![Single Stored Procedure for Select Insert Update Delete in SQL Server]()
DELETE Operation
When performing DELETE operation, the value of the @Action parameter is passed as DELETE and the value of @CustomerId is passed while @Name and @Country parameters are not supplied.
EXEC [dbo].[Customers_CRUD]@Action = 'DELETE'
,@CustomerId = 5
![Single Stored Procedure for Select Insert Update Delete in SQL Server]()
Thus this article clearly explains how to perform Select, Insert, Update and Delete operations using Single Stored Procedure in SQL Server.
