Professional SQL Server™ 2005 Programming

10.4. Dynamic SQL: Generating Your Code on the Fly with the EXEC Command

Okay, so all this saving stuff away in scripts is all fine and dandy, but what if you don't know what code you need to execute until runtime?

As a side note, notice that we are done with SQLCMD for now — the following examples should be run utilizing the Management Console.

SQL Server allows us, with a few gotchas, to build our SQL statement on the fly using string manipulation. The need to do this usually stems from not being able to know the details about something until runtime. The syntax looks like this:

EXEC ({<string variable>|'<literal command string>'})

Or:

EXECUTE ({<string variable>|'<literal command string>'})

As with executing a stored proc, whether you use the EXEC or EXECUTE makes no difference.

Let's build an example in the AdventureWorks database by creating a dummy table to grab our dynamic information out of:

USE AdventureWorks
GO

--Create The Table. We'll pull info from here for our dynamic SQL
CREATE TABLE DynamicSQLExample
(
   TableID     int   IDENTITY   NOT NULL
      CONSTRAINT PKDynamicSQLExample
                 PRIMARY KEY,
   SchemaName varchar(128)      NOT NULL,
   TableName varchar(128)       NOT NULL
)
GO

/* Populate the table. In this case, We're grabbing every user
** table object in this database                             */
INSERT INTO DynamicSQLExample
SELECT s.name AS SchemaName, t.name AS TableName
   FROM sys.schemas s
   JOIN sys.tables t
    ON s.schema_id = t.schema_id

This should get us a response something like:

(75 row(s) affected)

To quote the ...