In this article I will look at some quick examples of dynamic SQL.
The Basics
Dynamic SQL itself is not a difficult concept. The real problem is the ease that it can be used. In a large database environment, you can get a developer that thinks he knows the correct way to write a T-SQL call, but instead, brings the database server to its knees with poorly written SQL.
Using a Table name as input
The first thing we will cover is how to write a stored procedure that takes a table name as its input. Here are two examples, based on the two ways to do dynamic SQL in Transact-SQL:

CREATE PROCEDURE general_select1

    @tblname sysname,

    @key     varchar(10)

AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT col1, col2, col3 ' +

              ' FROM dbo.' + quotename(@tblname) +

              ' WHERE keycol = @key '

EXEC sp_executesql @sql, N'@key varchar(10)', @key

CREATE PROCEDURE general_select2

    @tblname nvarchar(127),

    @key     varchar(10)

AS

EXEC('SELECT col1, col2, col3

      FROM ' + @tblname + '

      WHERE keycol = ''' + @key + '''')

These are examples of bad usage of dynamic SQL. Passing a table name as a parameter is not how you should write stored procedures. Also, the two examples are not the same as each other. While both examples are bad, the second example has several problems that the first does not.
While the examples above look very simple and easy, there are some very important things to take a look at. The first thing is permissions. When you use stored procedures, users do not need permissions to access the tables accessed by the stored procedure. This does not apply when you use dynamic SQL! For the procedures above to execute successfully, the users must have SELECT permission on the table in @tblname.
The next problem is that the dynamic SQL is not part of the stored procedure, but constitutes its own scope. Scope in the case of SQL Server stored procedures is the memory space the stored procedure executes in. The stored procedure general_select2 cannot see the results of the execute on the dynamic SQL. Executing a block of dynamic SQL is the same as calling a nameless stored procedure created ad-hoc. This has a number of consequences:

Within the block of dynamic SQL, you cannot access local variables (including table variables) or parameters of the calling stored procedure. But you can pass parameters – in and out – to a block of dynamic SQL if you use sp_executesql.
Any USE statement in the dynamic SQL will not affect the calling stored procedure.
Temp tables created in the dynamic SQL will not be accessible from the calling procedure since they are dropped when the dynamic SQL exits. The block of dynamic SQL can, however, access temp tables created by the calling procedure.
If you issue a SET command in the dynamic SQL, the effect of the SET command lasts for the duration of the block of dynamic SQL only and does not affect the caller.
The query plan for the stored procedure does not include the dynamic SQL. The block of dynamic SQL has a query plan of its own.

While none of these things are "show stoppers," they can significantly affect the performance of your SQL Server. They also limit the way you can access the data returned from the dynamic SQL call from within the stored procedure itself.
We used two ways to make the dynamic SQL call. In application code, sp_executesql should be your choice 95% of the time. For now I will only give you two key issues: SQL Injection and Query-Plan Reuse. EXEC() is mainly useful for quick throw-away things and DBA tasks.
For the next, and final, example, we will be using sp_executesql to execute the dynamic SQL.
Taking it to another level
The following is an example, a rather long one, of dynamic SQL that serves a useful purpose and is well designed. (Please look in the SQL for the comments so you can understand what it is doing) It is part of a larger stored procedure. It takes parameters, builds and executes 2 dynamic SQL statements, and finally, returns a limited result set based on the parameters passed in. It also uses a concept new in SQL 2005 of a row number. The user passes in a result set size in number of records, starting row, page, and some other parameters that they want data for and get back only that data. This method is good for returning limited data to web sites.
    IF @Mode = 'J'
        BEGIN
            -- Get the total number of rows based on the filter
            SET @WhereClause = ''
          SET @Sql = 'SELECT @TotalRows_OUT = COUNT(HSEA_ID)
                FROM vwHSEA_ActionsRegister
                WHERE '
-- the filter here has be reduced to 2 parameters
-- for the sake of brevity
            IF @HSEA_Project_ID IS NOT NULL
                SET @WhereClause = @WhereClause +
                    ' AND HSEA_Project_ID = ' +
                    CAST(@HSEA_Project_ID AS VARCHAR(10))
            IF @HSEA_HZP_ID IS NOT NULL
                SET @WhereClause = @WhereClause +
                    ' AND HSEA_HZP_ID = ' +
                    CAST(@HSEA_HZP_ID AS VARCHAR(10))
            IF @WhereClause IS NOT NULL
                -- here we trim off the trailing AND
                SET @WhereClause = SUBSTRING(@WhereClause, 5,
                                   LEN(@WhereClause))
-- Here we set the output parameter so we can
-- see the result of our dynamic SQL
            SET @ParmDefinition = '@TotalRows_OUT int OUTPUT'
            -- combine our base SQL with the WHERE clause
            SET @Sql = @Sql + @WhereClause
            -- Execute the SQL statement we built
            EXEC sp_ExecuteSQL @Sql,
                 @ParmDefinition,
                 @TotalRows_OUT = @TotalRows
                 OUTPUT
            -- set the starting and ending rows
            IF ( @PageNumToReturn IS NULL )
                SET @PageNumToReturn = 1
-- in the next section, we will use a special ability
-- of SQL Server 2005 to return row numbers within
-- the result set
            IF @OrderByClause IS NULL
                SET @OrderByClause = ''
            -- notice how we are setting a start and end row here
            SET @StartRow = ( ( @PageNumToReturn - 1 ) *
                            @RowsToReturn ) + 1
            SET @EndRow = @StartRow + ( @RowsToReturn - 1 ) ;
-- here we build the actual SQL statement that we
--will end up executing
            SET @Sql = 'WITH    HSEA_Actions_Paged
                      AS ( SELECT TOP 100 PERCENT ROW_NUMBER()
                      OVER ( ORDER BY HSEA_ID ASC ) AS RowNumber ,' +
                      CAST(@TotalRows AS VARCHAR(10)) + '
                      AS NumberOfRows,' + '
                            HSEA_ID, HSEA_Project_ID,
                            HSEA_HZP_ID, HSEA_No,
                            HSEA_No_String, HSEA_By,
                            HSEA_By_Name, HSEA_Date,
                            HSEA_Status, HSEA_Description,
                            ProjectNo, ProjectName,
                            ClientShortName
                FROM vwHSEA_ActionsRegister'
            -- now we check to see if we have a WHERE clause
            IF @WhereClause IS NOT NULL
                SET @Sql = @Sql + ' WHERE ' +
                           @WhereClause + ') ' + CHAR(13)
            ELSE
                SET @Sql = @Sql + ') ' + CHAR(13)
            -- here we add a subselect to limit the data returned
            SET @Sql = @Sql + 'SELECT *
                FROM    HSEA_Actions_Paged
                WHERE   (RowNumber BETWEEN ' +
                        CAST(@StartRow AS VARCHAR(10)) +
                        ' AND ' + CAST(@EndRow AS VARCHAR(10)) + ')'
            IF @WhereClause IS NOT NULL
                SET @Sql = @Sql + ' WHERE ' +
                           @WhereClause + ') ' + CHAR(13)
            ELSE
                SET @Sql = @Sql + ') ' + CHAR(13)
            -- not we add an ORDER BY clause
            SET @Sql = @Sql + ' ' + @OrderByClause

-- and here we execute the SQL, returning a limited
-- number of rows
            EXEC sp_executesql @Sql
        END
As you can see from the example above, you can do some amazing things with dynamic SQL. But, you do need to be careful when you use it.
Robert L. Stewart is a full time DBA and SQL Server programmer at a small company outside of Houston. He has been involved with HAL-PC since MS Access 1.0 came out, and, has been the SIG leader for most of the Access SIGs. He is also the SIG leader for the Digital Imaging SIG that meets at the HAL-PC offices. He can be reached at robert@WeBeDB.com if you have any questions.