10. Dynamic SQL

Dynamic SQL allows SQL code to be constructed and executed dynamically at runtime. It is particularly useful in situations where SQL queries must be generated dynamically, such as when table names, columns, or other parameters are not known in advance. However, it must be handled with caution to prevent SQL injection vulnerabilities.

Example Usage of Dynamic SQL

1. Declaring Variables and Executing a Simple Query

-- Declare a variable for a specific value
DECLARE @Spec_LTP VARCHAR(75); -- varchar limit is up to 255
SET @Spec_LTP = 6.20;
 
-- Execute a query with the variable
SELECT * FROM [CSEDB].[dbo].[Prices] WHERE LTP = @Spec_LTP;

---

2. Printing and Executing Queries

-- Declare variables for dynamic SQL
DECLARE @Select NVARCHAR(75) = 'SELECT * FROM ';
DECLARE @TableName NVARCHAR(50) = '[CSEDB].[dbo].[Prices]';
 
-- Print the query
PRINT(@Select + @TableName);
 
-- Execute the query
EXEC(@Select + @TableName);

---

3. Writing a Simple Query Using Dynamic SQL

DECLARE @TableName2 NVARCHAR(50) = '[CSEDB].[dbo].[Prices]';
 
-- Execute a query dynamically
EXEC('SELECT * FROM ' + @TableName2);

---

4. Creating a Stored Procedure for Dynamic SQL

Create Procedure

GO
CREATE PROC GeneralQuery @TableName3 NVARCHAR(50)
AS
BEGIN
    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = 'SELECT * FROM ' + @TableName3;
    EXEC (@SQL);
END
GO

Execute the Stored Procedure

EXEC GeneralQuery '[CSEDB].[dbo].[Prices]';
EXEC GeneralQuery '[CSEDB].[dbo].[Companies]';

---

5. Altering (Editing) an Existing Stored Procedure

Alter Procedure

GO
ALTER PROC GeneralQuery @TableName3 NVARCHAR(50)
AS
BEGIN
    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = 'SELECT * FROM ' + @TableName3;
 
    -- Print the query for debugging purposes
    PRINT (@SQL);
 
    -- Execute the query
    EXEC (@SQL);
END
GO

Execute the Altered Stored Procedure

EXEC GeneralQuery '[CSEDB].[dbo].[Prices]';
EXEC GeneralQuery '[CSEDB].[dbo].[Companies]';

---

6. Checking and Dropping Stored Procedures

List All Procedures for Manual Dropping

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.name + '];'
FROM sys.procedures p;

Drop a Single Procedure

DROP PROCEDURE [dbo].[GetBookBorrowers];

Drop All Procedures at Once

DECLARE @procName VARCHAR(500);
DECLARE cur CURSOR FOR 
SELECT [name] 
FROM sys.objects 
WHERE type = 'P';
 
OPEN cur;
FETCH NEXT FROM cur INTO @procName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('DROP PROCEDURE [' + @procName + ']');
    FETCH NEXT FROM cur INTO @procName;
END;
 
CLOSE cur;
DEALLOCATE cur;

---

Key Considerations

1. Security Risks:

   • Dynamic SQL can be vulnerable to SQL injection if user inputs are directly included in the query.
   • Always sanitize inputs or use sp_executesql with parameterized queries for enhanced security.

2. Debugging:

   • Use PRINT statements to output the dynamically generated SQL query to debug issues.

3. Flexibility:

   • Dynamic SQL is ideal for scenarios where table names or columns need to be passed dynamically.

4. Performance:

   • Queries executed via dynamic SQL may not benefit from cached execution plans, which could impact performance.

5. Best Practices:

   • Limit the use of dynamic SQL to scenarios where no alternative exists.
   • Always use sp_executesql to parameterize input values when possible.