You must create a set of SQL Server stored procedures known as CRUD (Create, Read, Update, Delete) stored procedures in order to quickly and securely interact with your database tables. CRUD operations are crucial in database systems because they enable data modification.

How can we build a SQL server insert stored procedure?
You can use the Create operation to add new records to the database. creating 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 = 'Peter',
    @ProjectManagerId = 'A51DC085-073F-4D3A-AFC8-ACE61B89E8C8',
    @ProjectDescription = 'This is a sample Management Software.',
    @StartDate = '2023-01-20 12:45:00.000';

How may a Read stored procedure be created in the SQL server?
The Read operation is used to retrieve data from the database. You can create a select stored procedure by using a straightforward, constraint-free SELECT statement to retrieve all the data from a table.

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

SQL

EXEC UpdateProject
    @Id=1,
    @ProjectName = 'TimeSystem Software',
    @ClientName = 'Peter',
    @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

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.