T-SQL Smarts: Executing Sp_executesql
In this third in a series on working smarter with T-SQL, let's give the built-in sp_executesql stored proc a go.
- By Joshua Jones
- 04/26/2010
In my previous posts, I've described some methods for generating and executing T-SQL dynamically. This time, we'll take a look at one final way of executing T-SQL, which is the built-in system stored procedure sp_executesql.
This stored procedure takes a T-SQL statement (that you've generated) and any associated parameters that you wish pass, and executes it in a new process. It then returns any output (again, if specified) back to the calling process. This can be very handy when writing T-SQL that has variables, or when stringing together sets of dynamic SQL.
Take a look at the this example:
DECLARE @sql nvarchar(1000)
DECLARE @columns varchar(75)
DECLARE @dbname sysname
DECLARE @parms nvarchar(500)
SET @columns = '[name], [compatibility_level], [recovery_model_desc]'
SET @dbname = 'Admin'
SET @parms = '@dbparm sysname'
SET @sql = 'SELECT ' + @columns + ' FROM sys.databases WHERE [name] = @dbparm'
EXECUTE sp_executesql @sql, @parms, @dbparm = @dbname
While in real life you'd never use this complicated of a query to retrieve this information, it illustrates the syntax of the stored procedure easily.
Initially, we must declare a string to be executed (@sql). In this case we also want to be able to list the columns we want (@columns), and we need to be able to specify a database value (@dbname). Finally, we need a variable to hold the definition of the variables we want to use inside of the dynamic SQL command. This has to be defined as an NVARCHAR, but can be any length.
Then, we set the values. @columns and @dbname are obvious. When we set @parms, we're really just defining variables the way we would in any script or stored procedure (without the DECLARE keyword).
Finally, we set the @sql variable, outlining our command. We just use simple string concatenation to combine explicit text with variable values to produce a string that will execute.
Finally, we execute sp_executesql, passing (in order) the command, the parameter definitions, and the value want to set for the “inner” variable; in this case, we're passing the value of @dbname to the dynamic variable @dbparm for use in the command.
We get back this simple result set:
name compatibility_level recovery_model_desc
----------- ------------------- -------------------
Admin 100 FULL
(1 row(s) affected)
Knowing the basic structure of this stored procedure, you can build dynamic commands, and have variables passed into those dynamic commands, in any number of ways.
As always, beware: Run-time compiled commands such as this could be an easy target for SQL injection attacks. Be sure to check your input, and thoroughly test your scripts/stored procedures before executing them in production environments.
Be safe, and have fun!
About the Author
Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.