listout checklist of dynamic sql query and stored procedure query

Below is a checklist of dynamic SQL queries and stored procedures with examples:

Dynamic SQL Queries:

Dynamic SELECT Statement:

DECLARE @sql_query NVARCHAR(MAX);
SET @sql_query = 'SELECT column1, column2 FROM table_name WHERE condition';
EXEC sp_executesql @sql_query;

Dynamic INSERT Statement:

DECLARE @sql_query NVARCHAR(MAX);
SET @sql_query = 'INSERT INTO table_name (column1, column2) VALUES (value1, value2)';
EXEC sp_executesql @sql_query;

Dynamic UPDATE Statement:

DECLARE @sql_query NVARCHAR(MAX);
SET @sql_query = 'UPDATE table_name SET column1 = value1 WHERE condition';
EXEC sp_executesql @sql_query;

Dynamic DELETE Statement:

DECLARE @sql_query NVARCHAR(MAX);
SET @sql_query = 'DELETE FROM table_name WHERE condition';
EXEC sp_executesql @sql_query;

Dynamic WHERE Clause:

DECLARE @column_name NVARCHAR(50) = 'column1';
DECLARE @search_value NVARCHAR(50) = 'value1';
DECLARE @sql_query NVARCHAR(MAX);

SET @sql_query = 'SELECT * FROM table_name WHERE ' + QUOTENAME(@column_name) + ' = ' + QUOTENAME(@search_value);
EXEC sp_executesql @sql_query;

Dynamic ORDER BY Clause:

DECLARE @sort_column NVARCHAR(50) = 'column1';
DECLARE @sql_query NVARCHAR(MAX);

SET @sql_query = 'SELECT * FROM table_name ORDER BY ' + QUOTENAME(@sort_column) + ' ASC';
EXEC sp_executesql @sql_query;

Stored Procedure Queries

Basic Stored Procedure:

CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
    SELECT * FROM Employees;
END;

Stored Procedure with Parameters:

CREATE PROCEDURE GetEmployeeByID
    @employeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @employeeID;
END;

Stored Procedure with OUTPUT Parameter:

CREATE PROCEDURE GetEmployeeCount
    @count INT OUTPUT
AS
BEGIN
    SELECT @count = COUNT(*) FROM Employees;
END;

Stored Procedure with RETURN Value:

CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    DECLARE @count INT;
    SELECT @count = COUNT(*) FROM Employees;
    RETURN @count;
END;

Dynamic SQL in Stored Procedure:

CREATE PROCEDURE DynamicSearch
    @column_name NVARCHAR(50),
    @search_value NVARCHAR(50)
AS
BEGIN
    DECLARE @sql_query NVARCHAR(MAX);

    SET @sql_query = 'SELECT * FROM table_name WHERE ' + QUOTENAME(@column_name) + ' = ' + QUOTENAME(@search_value);
    EXEC sp_executesql @sql_query;
END;

Error Handling in Stored Procedure:

CREATE PROCEDURE InsertEmployee
    @name NVARCHAR(50),
    @age INT
AS
BEGIN
    BEGIN TRY
        INSERT INTO Employees (Name, Age) VALUES (@name, @age);
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

Cursor in Stored Procedure:

CREATE PROCEDURE ProcessEmployees
AS
BEGIN
    DECLARE @employeeName NVARCHAR(50);
    DECLARE employee_cursor CURSOR FOR
    SELECT Name FROM Employees;

    OPEN employee_cursor;
    FETCH NEXT FROM employee_cursor INTO @employeeName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Process each employee
        PRINT @employeeName;
        FETCH NEXT FROM employee_cursor INTO @employeeName;
    END

    CLOSE employee_cursor;
    DEALLOCATE employee_cursor;
END;

Transaction in Stored Procedure:

CREATE PROCEDURE TransferFunds
    @fromAccount INT,
    @toAccount INT,
    @amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;

    UPDATE Accounts SET Balance = Balance - @amount WHERE AccountID = @fromAccount;
    UPDATE Accounts SET Balance = Balance + @amount WHERE AccountID = @toAccount;

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK;
        PRINT 'Transaction failed. Rolling back changes.';
    END
    ELSE
    BEGIN
        COMMIT;
        PRINT 'Transaction successful. Changes committed.';
    END
END;

These examples cover a range of dynamic SQL queries and stored procedure scenarios. Always consider security aspects, parameterization, and error handling when working with dynamic SQL and stored procedures.

how-to-use-stored-procedure-to-prevent-sql-injection-attacks-in-laravel