Dynamic T-SQL

Dynamic SQL describes any SQL DML statement assembled dynamically at runtime as a string and then submitted

Generate code at runtime is very useful for several tasks:

- Multiple possible query criteria can be dynamically assembled into custom FROM, WHERE, and ORDER BY clauses for flexible queries

- Code can respond to the schema of the database and generate appropriate triggers, CRUD stored procedures, and views

- Dynamic code can auto-generate very consistent stored procedures

In this example we have the next data

and two stored procs which operate on our table CUSTOMERS: the first returns the result set of a SELECT statement with the WHERE clause built dinamically, and the second one updates the indicated field of those records with a certain condition

This is the stored proc for select data

     
	CREATE PROCEDURE SELECT_CUSTOMERS
    (@column_1 VARCHAR(20), @operator_1 VARCHAR(4), @value_1 VARCHAR(50),
     @column_2 VARCHAR(20), @operator_2 VARCHAR(4), @value_2 VARCHAR(50),
     @order_column VARCHAR(20))
AS BEGIN
    
    DECLARE @select_command varchar(2000)

    SET @select_command = 'SELECT FirstName, LastName, Telephone, Email
            FROM C_CUSTOMERS
            WHERE '  + @column_1 + ' ' + @operator_1 +  ' ''' + @value_1 + '' 
            + ''' AND ' + @column_2 + ' ' + @operator_2 + ' ''' + @value_2 + ''''
            + ' ORDER BY ' + @order_column
            
    CREATE TABLE #temp (FirstName VARCHAR(20), LastName VARCHAR(40), 
        Telephone VARCHAR(14), Email VARCHAR(20))

    INSERT INTO #temp EXEC (@select_command)

    SELECT * FROM #temp

    DROP TABLE #temp
    
    RETURN

END
GO

The result of the calling to this proc is the next:

SELECT_CUSTOMERS 'firstname', 'like', 'A%', 'telephone', 'like', '%207%', 'lastname'

This is the stored proc for update data

     
CREATE PROCEDURE UPDATE_CUSTOMERS
   (@column VARCHAR(20), @value VARCHAR(40), @where_clause VARCHAR(200))
AS BEGIN

    DECLARE @update_command varchar(2000)

    SET @update_command = 'UPDATE C_CUSTOMERS SET ' + @column 
        + ' = ''' + @value + ''''
        + 'WHERE ' + @where_clause
    
    EXEC (@update_command)
    
    SELECT CAST(@@ROWCOUNT AS VARCHAR) + ' customers updated' AS Result

END
GO

and the result of the calling is the next:

UPDATE_CUSTOMERS 'email', '----', 'email like ''%test%'''

<METHOD SOFTWARE © 2013>