SQL-Stored Procedure Example

Creating a single stored procedure that can handle INSERT, UPDATE, DELETE, and SELECT operations is possible by using a parameter to specify the action. Here's a general example:

Stored Procedure: sp_CRUDData

CREATE PROCEDURE sp_ManageData
    @Action NVARCHAR(10),         -- 'INSERT', 'UPDATE', 'DELETE', or 'SELECT'
    @ID INT = NULL,               -- ID of the record (used for SELECT, UPDATE, DELETE)
    @Name NVARCHAR(100) = NULL,   -- Name (used for INSERT, UPDATE)
    @Value NVARCHAR(100) = NULL   -- Value (used for INSERT, UPDATE)
AS
BEGIN
    SET NOCOUNT ON;

    IF @Action = 'INSERT'
    BEGIN
        INSERT INTO YourTable (Name, Value)
        VALUES (@Name, @Value);
    END
    ELSE IF @Action = 'UPDATE'
    BEGIN
        UPDATE YourTable
        SET Name = @Name,
            Value = @Value
        WHERE ID = @ID;
    END
    ELSE IF @Action = 'DELETE'
    BEGIN
        DELETE FROM YourTable
        WHERE ID = @ID;
    END
    ELSE IF @Action = 'SELECT'
    BEGIN
        IF @ID IS NOT NULL
        BEGIN
            SELECT * FROM YourTable WHERE ID = @ID;
        END
        ELSE
        BEGIN
            SELECT * FROM YourTable;
        END
    END
    ELSE
    BEGIN
        PRINT 'Invalid action. Please specify INSERT, UPDATE, DELETE, or SELECT.';
    END
END

Explanation

1. Parameters
   • @Action: Specifies the operation (INSERT, UPDATE, DELETE, SELECT).
   • @ID: Identifies the record (used for UPDATE, DELETE, or to fetch specific records in SELECT).
   • @Name and @Value: Used for INSERT and UPDATE operations.
2. Logic
   • Depending on the value of @Action, the procedure executes the appropriate SQL operation.
3. Default Handling
   • If no valid @Action is provided, an error message is printed.

Usage Examples

1. Insert a record

EXEC sp_CRUDData @Action = 'INSERT', @Name = 'ExampleName', @Value = 'ExampleValue';

2. Update a record

EXEC sp_CRUDData @Action = 'UPDATE', @ID = 1, @Name = 'UpdatedName', @Value = 'UpdatedValue';

3. Delete a record

EXEC sp_CRUDData @Action = 'DELETE', @ID = 1;

4. Select a record

EXEC sp_CRUDData @Action = 'SELECT', @ID = 1;