How to Create Stored Procedures for CRUD?

Introduction

In order to easily and securely interact with your database tables, you must write a set of procedures known as CRUD (Create, Read, Update, Delete) stored procedures in SQL Server. As they allow for data modification, CRUD procedures play an important role in database applications.

How we can create an insert stored procedure in the SQL server?

To add new records to the database, utilize the Create operation. To develop a stored procedure

CREATE PROCEDURE InsertProject
    @ProjectName NVARCHAR(500),
    @ClientName NVARCHAR(500),
    @ProjectManagerId NVARCHAR(500),
    @ProjectDescription NVARCHAR(500) = NULL,
    @StartDate DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Projects
    (
        [ProjectName],
        [ClientName],
        [ProjectManagerId],
        [CreatedDate],
        [ProjectDescription],
        [StartDate]
    )
    VALUES
    (
        @ProjectName,
        @ClientName,
        @ProjectManagerId,
        GETUTCDATE(),
        @ProjectDescription,
        @StartDate
    )
END

EXEC InsertProject
    @ProjectName = 'Management Software',
    @ClientName = 'Shubham Mishra',
    @ProjectManagerId = 'A51DC085-073F-4D3A-AFC8-ACE61B89E8C8',
    @ProjectDescription = 'This is a sample Management Software.',
    @StartDate = '2023-01-20 12:45:00.000';

How we can create a Read stored procedure in the SQL server? 

Retrieving data from the database is done using the Read operation. To develop a select stored procedure, you can use a simple SELECT statement without any constraints to get all the data from a table within a stored procedure.

CREATE PROCEDURE GetAllProject
   
AS
BEGIN
    SET NOCOUNT ON; 
   SELECT Id, ProjectName, ClientName,ProjectDescription,StartDate,EndDate,CreatedDate from Projects
END

EXEC GetAllProject

Output

Use a WHERE clause with the proper condition to retrieve a specific row from the table using a unique identifier (such as Id) within a stored procedure.

CREATE PROCEDURE GetProjectByProjectId
     @Id INT
AS
BEGIN
    SET NOCOUNT ON; 
   SELECT ProjectName,ClientName,ProjectDescription,CreatedDate from Projects
      WHERE Id = @Id;
END

EXEC GetProjectByProjectId @Id=2

Output

How we can create an Update stored procedure in the SQL server?

Existing records in the database can be modified using the Update operation. 

CREATE PROCEDURE [UpdateProject]
     @id INT
	,@ProjectName NVARCHAR(500)
	,@ClientName NVARCHAR(500)
	,@ProjectManagerId NVARCHAR(500)
	,@ProjectDescription NVARCHAR(500)
	,@StartDate DATETIME = NULL
	,@EndDate DATETIME = NULL
	,@UpdatedDate DATETIME = NULL
AS
BEGIN
	UPDATE Projects
	SET ProjectName = @ProjectName
		,ClientName = @ClientName
		,ProjectManagerId = @ProjectManagerId 
		,ProjectDescription = @ProjectDescription
		,StartDate = @StartDate
		,EndDate = @EndDate
		,UpdatedDate = getutcdate()
	WHERE Id = @Id
END

EXEC UpdateProject
    @Id=1,
    @ProjectName = 'TimeSystem Software',
    @ClientName = 'Shubham Mishra',
    @ProjectManagerId = 'A51DC085-073F-4D3A-AFC8-ACE61B89E8C8',
    @ProjectDescription = 'This is a sample TimeSystem Software.',
    @StartDate = '2023-01-20 12:45:00.000',
    @EndDate = '2023-04-20 12:45:00.000',
    @UpdatedDate= getutcdate();

Output

How we can create a Delete stored procedure in the SQL server?

To delete records from the database, use the Delete operation. To develop a stored process for deletion

CREATE PROCEDURE DeleteProjectById
   @id int
AS
BEGIN
    SET NOCOUNT ON; 

    DELETE FROM Projects
    WHERE id = @id;
END

EXEC DeleteProjectById @Id=2

Output

To preserve accurate information and past information, gentle deletion rather than hard deletion is frequently used in database designs. Instead of physically removing records from the database, soft delete involves listing them as inactive or removed. This strategy enables previous tracking and data retrieval.

CREATE PROCEDURE [DeleteProject]
@id int 
AS
BEGIN

UPDATE Projects SET IsDelete =1, IsActive =0 
WHERE Id = @id

END

EXEC DeleteProject @Id=2

Output

Conclusion 

You may efficiently interact with your database tables while following security and performance best practices by developing CRUD stored procedures. Stored procedures reduce the risk of SQL injection flaws, increase database performance, and allow code reuse. 

If you have any queries/suggestions on the article, please leave your questions and thoughts in the comment section below. Follow C# Corner to learn more new and amazing things about SQL Server or to explore more technologies.

Thanks for reading, and I hope you like it.

FAQs

Q. What is a CRUD stored procedure in SQL Server?

Ans. A CRUD stored procedure is a set of SQL statements encapsulated in a named block, allowing developers to perform Create, Read, Update, and Delete operations on a database table efficiently and securely.

Q. Why should I use stored procedures for CRUD operations?

Ans. Using stored procedures provides several benefits, including improved performance, reduced network traffic, enhanced security (preventing SQL injection), and easier maintenance.

Q. How do I create a CRUD stored procedure in SQL Server?

Ans. To create a CRUD stored procedure, use the CREATE PROCEDURE statement with input parameters for data manipulation. Write SQL statements for each operation, such as INSERT, SELECT, UPDATE, and DELETE.

Q. What are the best practices for writing CRUD stored procedures?

Ans. Best practices include parameterizing input, validating user input to prevent SQL injection, and handling errors with TRY...CATCH blocks, and consider soft deletes instead of hard deletes for better data integrity.

Q. How do I write a stored procedure for inserting data?

Ans. To write an insert stored procedure, use the INSERT INTO statement with input parameters for the values to be inserted. Handle errors and return useful messages.

Q. How do I write a stored procedure for reading data?

Ans. For reading data, use a SELECT statement with input parameters, such as an ID for fetching specific rows. Consider pagination for large result sets.

Q. How do I write a stored procedure for updating data?

Ans. To write an update stored procedure, use the UPDATE statement with input parameters to modify specific rows based on a unique identifier (e.g., ID).

Q. How do I write a stored procedure for deleting data?

Ans. For a delete stored procedure, use the DELETE statement with input parameters representing the identifier (e.g., ID) of the record to be removed.

Q. Can I use transactions in CRUD stored procedures?

Ans. Yes, you can use transactions in CRUD stored procedures to ensure data consistency and handle multiple operations as a single unit.

Q. Is it recommended to use dynamic SQL in CRUD stored procedures?

Ans. Avoid using dynamic SQL in CRUD stored procedures whenever possible, as it can introduce security vulnerabilities and make the code harder to maintain. Instead, use parameterized queries for safety and performance.