A useful technique when you need utmost flexibility in a T-SQL application is to build the code dynamically and execute it on the fly. Microsoft has made such dynamic execution far more efficient than in earlier versions of SQL Server, such as by caching the execution plan for multiple executions. The danger, of course, is that building a SQL statement on the fly and concatenating parameter values is that it opens up the app to SQL injection attacks, so it should be used with caution and careful validation of any user-controllable input. Dynamic SQL also has different security issues. SQL Server will always validate the user's permissions on the underlying objects, even though there is an unbroken ownership chain from the stored procedure through objects referenced.
You can execute SQL dynamically using either the EXECUTE
T-SQL statement or the sp_executesql
stored procedure. sp_executesql
is the more flexible of the two though, because it supports using parameters. The unlimited, comma-delimited list of parameters can include the data type so that SQL Server will do data type validation.
shows an example of sp_executesql
that reads data from the Customers
table in the Northwind database. This example uses a string of parameter names and data types in the @ParmDefinition
string, including both a single input and a single output value. The single input parameter is in the @CustomerID
variable. Notice too how the crux of the code, where the dynamic SQL is actually executed, uses the EXECUTE
statement to actually run the code. This is an interesting example of combining the two statements that shows how each is used.
One thing you have to be careful of with dynamic SQL is the context of the statements. Consider this code.
sp_executesql N'USE Northwind'
SELECT * FROM Shippers
This batch starts by making pubs
the default database and then executing a dynamic SQL statement that sets Northwind as the default database. Once the dynamic SQL has finished executing and by the time the SELECT
statement executes, the database context is back in the pubs
database, so the SELECT
Sometimes it is handy to find out things about the environment you're running in. sp_server_info
returns a list of attributes about the SQL Server instance, the database gateway in use, or the underlying data source. The attributes returned include such mundane things as OWNER_TERM
for what the database calls an owner (hopefully it is not a shock that it is "owner" for SQL Server), maximum length of table and other object names, whether certain operations are permitted, and the size of things such as the maximum number of columns in an index.
You can call sp_server_info
in either of two ways. Without parameters, it returns a recordset with the complete list of attributes, including the attribute ID (an int), its name, and the current value. Or you can specify the attribute ID to return a single record with the attribute's information.
sp_server_info @attribute_id = 2
This is one of those rather interesting anomalies in SQL Server. According to BOL, sp_procoption
"sets procedure options." The interesting part is that "s" at the end of "options." Despite the description, sp_procoption
sets exactly one option to configure a stored procedure for auto execution when SQL Server starts up. Only objects in the master
database owned by dbo
can have their startup setting changed, and that change is restricted to stored procedures with no parameters.
It is easy enough to use.
USE master -- only in master
EXEC sp_procoption 'sp_IndexRebuild',
Simply pass the name of the stored procedure, specify the procedure option you wantthe only option is 'startup'and set to true
to auto execute or false
to prevent auto-execution.
According to the latest beta documentation I have for SQL Server 2005, there is still only going to be a single option for this procedure in the next version. One can only guess at why there is such a procedure seemingly designed for multiple options! It is particularly strange since this procedure replaced two other system stored procedures, sp_makestartup
, in older versions of SQL Server. If anyone knows the story about this one trick pony, I'd love to hear it.