How to Create Stored Procedures for CRUD?

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';

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

EXEC GetAllProject

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

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();

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.