Execute(ANY STRING)

The ability to formulate and then execute a string dynamically in SQL Server is a subtle but powerful capability. Using this capability, you can avoid additional round-trips to the client application by formulating a new statement to be executed directly on the server. You can pass a string directly, or you can pass a local variable of type char or varchar. This capability is especially useful if you need to pass an object name to a procedure or if you want to build an SQL statement from the result of another SQL statement.

For example, suppose that we had partitioned our database to have multiple tables similar to the authors table. We could write a procedure like the one shown below to pass the name of the table we want to insert into. The procedure would then formulate the INSERT statement by concatenating strings, and then it would execute the string it formulated:

 CREATE PROC add_author @au_id char(11), @au_lname varchar(20), @au_fname varchar(20), @tabname varchar(30) AS BEGIN DECLARE @insert_stmt varchar(255) SELECT @insert_stmt='INSERT ' + @tabname + ' (au_id, au_lname, au_fname, contract) VALUES (''' + @au_id + ''',''' + @au_lname + ''',''' + @au_fname + ''', 1)' -- PRINT @insert_stmt EXECUTE (@insert_stmt) END EXEC add_author '999-99-1234', 'Pike', 'Neil', 'authors' 

Note that the procedure uses lots of single quotes. I didn't want to use double quotes because the option QUOTED_IDENTIFIER is ON by default in SQL Query Analyzer, and that means you can use double quotes only to indicate object names. Two single quotes together, inside of a string, will be interpreted as a single quote. It's the way to "quote the quote." In addition, you might notice that I included a simple debugging technique. I commented a statement that will actually show me the value I built in the string @insert_stmt. If SQL Server complains of syntax errors when the procedure is executed and I just can't figure out where the error is, I can ALTER the procedure, uncomment the PRINT statement, and put the comment marks in front of the EXECUTE statement.

If you make use of this dynamic EXECUTE inside a routine, there are a few additional issues that you need to be aware of:

• The plan for the SQL statement constructed from the string is not saved as part of the routine's plan. If the values of any of the variables or parameters used to build the SQL string change, the SQL statement will need to be recompiled. This means you won't always get the full benefit of having the plans for routines precompiled.
• Although SQL Server can use variables from outside the EXEC string to construct the SQL statement, any variables assigned a value in the constructed string will not be available outside the EXEC.
• A USE statement issued to change your database context is in effect only for the EXEC string's execution. The example below illustrates this behavior:

   SET NOCOUNT ON USE pubs PRINT db_name() EXEC ('USE Northwind PRINT db_name() SELECT count(*) FROM products') PRINT db_name() GO RESULTS: pubs Northwind ----------- 77 pubs 

• Normally, if a user has permission to execute a stored procedure or function, that user will also have implied permission to access all objects in the routine owned by the routine's owner. This does not apply to objects accessed inside an EXEC string. Even if a user has permission to execute a procedure, if the user doesn't have permission to perform all the actions specified in the EXEC string, a run-time error will occur.
• The only operation that you can use to build the string inside the EXEC is the concatenation operator. You cannot use functions. For example, if you want to select from a table that has the name of the user plus the day of the month for its name (for example: kalen20), you must build the string to be executed outside of the EXEC. Here's an example:

   DECLARE @sqlstring varchar(100) SELECT @sqlstring = 'SELECT * FROM ' + LTRIM(user_name()) + CONVERT(char(2),DATEPART(dd, GETDATE())) EXEC (@sqlstring)