Creating a Stored Procedure with Dynamic SQL

Create a Stored Procedure with Dynamic SQL

Write a SQL query to create a stored procedure that uses dynamic SQL to execute a query based on user input.

Solution:

-- Create a stored procedure with dynamic SQL.
CREATE PROCEDURE ExecuteDynamicQuery
    @TableName NVARCHAR(128), -- Input parameter for the table name.
    @FilterColumn NVARCHAR(128), -- Input parameter for the filter column.
    @FilterValue NVARCHAR(128) -- Input parameter for the filter value.
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);

    -- Construct the dynamic SQL query.
    SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName) + 
               ' WHERE ' + QUOTENAME(@FilterColumn) + ' = @FilterValue';

    -- Execute the dynamic SQL query.
    EXEC sp_executesql @SQL, N'@FilterValue NVARCHAR(128)', @FilterValue;
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a stored procedure that dynamically constructs and executes a SQL query based on user-provided parameters.

2. Key Components :

1. QUOTENAME : Ensures safe handling of table and column names to prevent SQL injection.
2. sp_executesql : Executes the dynamic SQL query with parameterized inputs.

3. Why Use Dynamic SQL? :

1. Dynamic SQL allows you to build flexible queries that adapt to varying user inputs or conditions.

4. Real-World Application :

1. For example, in reporting systems, you might use this procedure to generate custom reports based on user-selected filters.

Additional Notes:

• Always sanitize inputs and use parameterized queries to prevent SQL injection attacks.
• Use this exercise to teach the risks and benefits of dynamic SQL in database programming.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a stored procedure that dynamically constructs and executes a SELECT query based on a list of column names provided by the user.
• Write a SQL query to create a stored procedure that builds and runs an INSERT statement dynamically based on user-supplied values.
• Write a SQL query to create a stored procedure that generates a DELETE statement dynamically to remove records matching specific criteria.
• Write a SQL query to create a stored procedure that dynamically alters a table schema based on user-defined parameters.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Create a Trigger for Cascading Updates.
Next SQL Exercise: Create a Stored Procedure with Pagination.