Sometimes you need dynamic SQL.  Sometimes you don't know at run time where you're querying or what fields you need to bring back, or what your criteria will be, so there will always be a place for the use of dynamic SQL.  You have to be careful how you use it, but if you do use it correctly it can be a powerful tool that you have in your tool belt. 

So, today I am going to show you a nice little general purpose procedure that you can write to pass in a few object strings and execute dynamic SQL.  Im not going to go into the ins and outs of low-level dynamic SQL or what you need to do to prevent certain types of SQL injection...I'll leave that discussion for a later time.

 First, lets just setup a table and insert some values into it.  

use tempdb

IF OBJECT_ID('DynamicQueries','U') IS NOT NULL
DROP TABLE DynamicQueries
GO
CREATE TABLE DynamicQueries
(
    ID SMALLINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Fld1 INT, 
    Fld2 INT
)
SET NOCOUNT ON

DECLARE @x SMALLINT
SET @x = 1

WHILE @x < 32000
BEGIN
    INSERT INTO DynamicQueries(Fld1, Fld2)
    SELECT @x % 10, @x % 7
    
    SET @x = @x + 1
END
GO

Below is our "general purpose" dynamic SQL stored procedure.  It requires a table name and a field list (technically you could provide * for the field list) and an optional parameter for the query criteria.  It has the limitation of only querying one table for the time being, but I may expand upon that idea at a later point to allow for the addition of other tables with joins, etc.  If you wanted to make this procedure really general purpose, you could make it a system stored procedure.  I didn't do it in this example, but you could easy just change the procedure name from usp_ to sp_, put it in the master database and mark it as a system stored procedure.  Then you'd be able to run this procedure in any database w/o having to worry about the database context.

CREATE PROCEDURE usp_GetDynamicValues
(
    @TableName NVARCHAR(255),
    @FieldList NVARCHAR(1000),
    @Criteria NVARCHAR(1000) = NULL
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    BEGIN TRY
        SET @SQL = N'SELECT ' + @FieldList + ' FROM ' + @TableName 
        SET @SQL = @SQL + 
                        CASE 
                            WHEN NULLIF(LTRIM(RTRIM(@Criteria)), '') IS NULL 
                            THEN '' 
                            ELSE ' WHERE ' + @Criteria 
                        END
                        
        EXECUTE sp_executesql 
        @stmt = @SQL
    END TRY
    BEGIN CATCH
        PRINT 'An error has occurred.'
    END CATCH
END
GO

 Notice in the above example that I am using sp_executesql rather than the EXECUTE command. sp_executesql allow for using parameters when executing dynamic sql statements. Doing so encapsulates your parameter values which can in some instances prevent SQL injection attacks.  The parameters also lead to better plan reuse than does the EXECUTE command. So, try to always use sp_executesql over EXECUTE.

 Once the procedure is created, all that is left to do is to call it, which I am doing below.   

EXECUTE usp_GetDynamicValues
@TableName = 'DynamicQueries', 
@FieldList = 'ID, Fld2', 
@Criteria = 'ID = 4'

 While this example was relatively simple, it shows you how useful (and powerful dynamic SQL can be).  Look for future articles where I expand upon this idea and start to develop a more elaborate setup where you can really harness the power of using dynamic SQL.

HTH,
Tim