mssql stored procedure create read update delete into database table Edit

In the ever-evolving landscape of database management, the role of stored procedures in Microsoft SQL Server (MSSQL) for CRUD (Create, Read, Update, Delete) operations remains crucial. These procedures offer a structured way to interact with the database, ensuring that data manipulation is both efficient and secure.Create operations, as the name suggests, are used to insert new records into a database table. Read operations, on the other hand, are about retrieving data from the database. This can range from fetching a single record to extracting a complex set of data, often involving sorting and pagination. Stored procedures for reading data can significantly optimize performance, especially in large databases, by minimizing the data sent over the network and reducing server load.Stored procedures for updating records provide a secure way to modify data, often including checks to ensure that only valid data is saved and that unauthorized changes are prevented. Delete operations, though seemingly straightforward, carry the highest risk as they involve removing data from the database.

Create

/****** Object:  StoredProcedure [dbo].[_sp_insert_template]    Script Date: 23-12-2023 09:18:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:      Murugan Andezuthu Dharmaratnam
-- Create Date: 2023
-- Description: Inserts a new entry into the DeliveryType table.
-- Revision Date / Comments:
-- =============================================
CREATE PROCEDURE [dbo].[_sp_insert_template]
(
	 @pId uniqueidentifier,
	 @pName nvarchar(max),
	 @pUserName nvarchar(max),
	 @pIPAddress nvarchar(max)
)
AS
BEGIN
    -- Prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Error handling
    BEGIN TRY
        -- Insert the new entry
	    INSERT INTO [dbo].[YourTableName]([Name])
	    VALUES (@pName);
    END TRY
    BEGIN CATCH
        -- Error handling
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Log error to an error log table if needed.
        -- INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine)
        -- VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, ERROR_PROCEDURE(), ERROR_LINE());

        -- Raise the error with the original error details.
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

Read rows of data from a table

/****** Object:  StoredProcedure [dbo].[_sp_get_templates_WithPaging]    Script Date: 23-12-2023 09:20:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:      Murugan Andezuthu Dharmaratnam
-- Create Date: 2023
-- Description: Description
-- Revision Dat / Comments:
-- =============================================
CREATE PROCEDURE [dbo].[_sp_get_templates_WithPaging]
(
	@pCurrentPage integer = 1,
	@pPageSize integer = 10,
	@pUserName nvarchar(max),
	@pName nvarchar(max)
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON


	declare @Skip integer = @pPageSize * (@pCurrentPage-1);

    -- Get statements for procedure here
	select TOP (@pPageSize) * from (
		-- Main select 
		select ROW_NUMBER() OVER (ORDER BY [dbo].[YourTableName].[Name]) AS ROW_NUM, * from [dbo].[YourTableName]
		where 1=1 
		and [Name] like '%'+@pName+'%' 
	) x where ROW_NUM between @Skip and @Skip+@pPageSize order by ROW_NUM, [Name]
	
	select  count(NumericId) as [Count] from [dbo].[YourTableName]
	where 1=1 
	and [Name] like '%'+@pName+'%' 


END
GO

Read From Table Given Id

/****** Object:  StoredProcedure [dbo].[_sp_get_template]    Script Date: 23-12-2023 09:23:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:      Murugan Andezuthu Dharmaratnam
-- Create Date: 2023
-- Description: Description
-- Revision Dat / Comments:
-- =============================================
CREATE PROCEDURE [dbo].[_sp_get_template]
(
	@pId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Get statements for procedure here
	select [dbo].[YourTableName].* from [dbo].[YourTableName] where [dbo].[YourTableName].Id = @pId

END
GO

Update Table

/****** Object:  StoredProcedure [dbo].[_sp_update_template]    Script Date: 23-12-2023 09:25:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- Author:      Murugan Andezuthu Dharmaratnam
-- Create Date: 2023
-- Description: Updates an entry in the DeliveryType table based on the Name.
-- Revision Date / Comments:
-- =============================================
CREATE PROCEDURE [dbo].[_sp_update_template]
(
	 @pId uniqueidentifier,
	 @pName nvarchar(max),
	 @pUserName nvarchar(max),
	 @pIPAddress nvarchar(max)

-- =============================================
)
AS
BEGIN
    -- Prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    BEGIN TRY
        -- Update the specified entry
	    UPDATE [dbo].[YourTableName]
        SET [Name] = @pName
        WHERE [Id] = @pID;
    END TRY
    BEGIN CATCH
        -- Error handling
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Log error to an error log table if needed.
        -- INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine)
        -- VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, ERROR_PROCEDURE(), ERROR_LINE());

        -- Raise the error with the original error details.
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

Update a Single Cell

/****** Object:  StoredProcedure [dbo].[_sp_update_updatecell_template]    Script Date: 23-12-2023 09:27:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- Author:      Murugan Andezuthu Dharmaratnam
-- Create Date: 2023
-- Description: Updates an entry in the DeliveryType table based on the Name.
-- Revision Date / Comments:
-- =============================================
CREATE PROCEDURE [dbo].[_sp_update_updatecell_template]
(
	@pId uniqueidentifier,	
	@pTableName nvarchar(max),
	@pColumnName nvarchar(max),
	@newValue nvarchar(max),
	@pUserName nvarchar(max),
	@pIPAddress nvarchar(max)
-- =============================================
)
AS
BEGIN
    -- Prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT OFF;

    BEGIN TRY
        -- Update the specified entry

		DECLARE @DynamicSQL NVARCHAR(MAX) = ''
		SET @DynamicSQL = @DynamicSQL + 'update ' + @pTableName + CHAR(13) + CHAR(10)
		SET @DynamicSQL = @DynamicSQL + 'set '  + @pColumnName + ' = ''' + @newValue + ''''  + CHAR(13) + CHAR(10) 
		SET @DynamicSQL = @DynamicSQL + 'where Id = '''  + CAST(@pId as nvarchar(max))+ ''' '
		-- Execute the dynamic SQL
		EXEC sp_executesql @DynamicSQL

    END TRY
    BEGIN CATCH
        -- Error handling
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Log error to an error log table if needed.
        -- INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine)
        -- VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, ERROR_PROCEDURE(), ERROR_LINE());

        -- Raise the error with the original error details.
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

Delete

/****** Object:  StoredProcedure [dbo].[_sp_delete_template]    Script Date: 23-12-2023 09:30:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:      Murugan Andezuthu Dharmaratnam
-- Create Date: 2023
-- Description: Deletes an entry from the DeliveryType table based on the Name.
-- Revision Date / Comments:
-- =============================================
CREATE PROCEDURE [dbo].[_sp_delete_template]
(
	@pId uniqueidentifier
)
AS
BEGIN
    -- Prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    BEGIN TRY
        -- Delete the specified entry
	    DELETE FROM [dbo].[YourTableName]
        WHERE [Name] = @pId;
    END TRY
    BEGIN CATCH
        -- Error handling
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Log error to an error log table if needed.
        -- INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine)
        -- VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, ERROR_PROCEDURE(), ERROR_LINE());

        -- Raise the error with the original error details.
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO