Usar sp_executesql

Para ejecutar una cadena, se recomienda utilizar el procedimiento almacenado sp_executesql, en lugar de una instrucción EXECUTE. Puesto que este procedimiento almacenado admite la sustitución de parámetros, sp_executesql es no sólo más versátil que EXECUTE, sino que, como además genera planes de ejecución con más probabilidades de que SQL Server los vuelva a utilizar, resulta más eficaz que éste.

Lotes independientes

Cuando sp_executesql o la instrucción EXECUTE ejecutan una cadena, ésta se ejecuta como su propio lote independiente. SQL Server compila la instrucción o instrucciones Transact-SQL de la cadena en un plan de ejecución independiente del plan de ejecución del lote que contenía sp_executesql o la instrucción EXECUTE. Para los lotes independientes se aplican las siguientes reglas:

  • Las instrucciones Transact-SQL de la cadena EXECUTE o sp_executesql no se compilan en un plan de ejecución hasta que la instrucción EXECUTE o sp_executesql se han ejecutado. Las cadenas no se analizan ni se comprueba que tengan errores hasta que se han ejecutado. Los nombres a los que se hace referencia en las cadenas no se resuelven hasta que se han ejecutado.

  • Las instrucciones Transact-SQL de la cadena ejecutada no tienen acceso a ninguna de las variables declaradas en el lote que contiene la instrucción EXECUTE o sp_executesql. El lote que contiene la instrucción EXECUTE o sp_executesql no tiene acceso a las variables o cursores locales definidos en la cadena ejecutada.

  • Si la cadena ejecutada contiene una instrucción USE que cambia el contexto de la base de datos, este cambio sólo dura hasta que finaliza la ejecución de sp_executesql o la instrucción EXECUTE.

La ejecución de los dos lotes siguientes muestra estos puntos:

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

Sustituir valores de parámetros

sp_executesql admite la sustitución de valores de parámetros para cualquier parámetro especificado en la cadena Transact-SQL, pero la instrucción EXECUTE no lo hace. Por tanto, las cadenas Transact-SQL generadas por sp_executesql son más similares que las generadas por la instrucción EXECUTE. El optimizador de consultas de SQL Server probablemente compare las instrucciones Transact-SQL de sp_executesql con los planes de ejecución de las instrucciones ejecutadas previamente, lo que ahorra la sobrecarga de trabajo que supone compilar un nuevo plan de ejecución.

En la instrucción EXECUTE, todos los valores de los parámetros se deben convertir a caracteres o datos Unicode y formar parte de la cadena Transact-SQL.

Si la instrucción se ejecuta varias veces, debe generarse una cadena Transact-SQL completamente nueva para cada ejecución, incluso cuando las únicas diferencias se encuentren en los valores suministrados para los parámetros. Esto genera una sobrecarga adicional de las formas siguientes:

  • La capacidad del optimizador de consultas de SQL Server para comparar la nueva cadena Transact-SQL con un plan de ejecución existente está obstaculizada por el cambio constante de los valores de los parámetros del texto de la cadena, especialmente en las instrucciones Transact-SQL complejas.

  • La cadena completa se debe volver a generar en cada ejecución.

  • Los valores de los parámetros (que no sean de caracteres ni Unicode) se deben convertir a un formato de caracteres o Unicode en cada ejecución.

sp_executesql admite que se establezcan valores en los parámetros, independientemente de la cadena Transact-SQL:

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql ofrece las siguientes ventajas adicionales:

  • Como el texto real de la instrucción Transact-SQL no cambia entre una ejecución y otra, el optimizador de consultas debe comparar la instrucción Transact-SQL de la segunda ejecución con el plan de ejecución generado para la primera instrucción. De este modo, SQL Server no tiene que compilar la segunda instrucción.

  • La cadena Transact-SQL sólo se genera una vez.

  • El archivo de datos tiene formato nativo. No es necesaria la conversión a Unicode.

    Nota

    Los nombres de objeto de la cadena de instrucciones deben aparecer completos para que SQL Server pueda volver a utilizar el plan de ejecución.

Volver a utilizar planes de ejecución

En las versiones anteriores de SQL Server, la única forma de volver a utilizar los planes de ejecución era definir las instrucciones Transact-SQL como un procedimiento almacenado y hacer que la aplicación lo ejecutara. Esto genera una sobrecarga administrativa adicional para las aplicaciones. La utilización de sp_executesql puede contribuir a reducir esta sobrecarga, al tiempo que permite que SQL Server vuelva a utilizar planes de ejecución. Se puede utilizar sp_executesql en lugar de los procedimientos almacenados cuando se ejecute una instrucción Transact-SQL varias veces y la única variación la constituyan los valores de los parámetros suministrados a la instrucción Transact-SQL. Al permanecer constantes las instrucciones Transact-SQL y variar sólo los valores de los parámetros, es probable que el optimizador de consultas de SQL Server vuelva a utilizar el plan de ejecución que genera para la primera ejecución.

En el siguiente ejemplo se genera y ejecuta una instrucción DBCC CHECKDB para cada base de datos de un servidor, excepto para las cuatro bases de datos del sistema.

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases;

DECLARE @DBNameVar NVARCHAR(128);
DECLARE @Statement NVARCHAR(300);

FETCH NEXT FROM AllDatabases INTO @DBNameVar;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar;
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY';
   EXEC sp_executesql @Statement;
   PRINT CHAR(13) + CHAR(13);
   FETCH NEXT FROM AllDatabases INTO @DBNameVar;
END;

CLOSE AllDatabases;
DEALLOCATE AllDatabases;
GO
SET NOCOUNT OFF;
GO

El controlador ODBC de SQL Server utiliza sp_executesql para implementar SQLExecDirect cuando la instrucción Transact-SQL que se está ejecutando contiene marcadores de parámetros enlazados. Esto permite ampliar las ventajas proporcionadas por sp_executesql a todas las aplicaciones que utilicen ODBC o API definidas sobre ODBC, como RDO. Las aplicaciones existentes de ODBC que se transfieren a SQL Server aumentan automáticamente su rendimiento sin que tengan que escribirse de nuevo. La única excepción es que sp_executesql no se usa con parámetros de datos de la ejecución. Para obtener más información, vea Usar parámetros de instrucciones.

El proveedor ODBC de SQL Server Native Client también utiliza sp_executesql para implementar la ejecución directa de instrucciones con parámetros enlazados. Las aplicaciones que usan OLE DB o ADO aprovechan las ventajas proporcionadas por sp_executesql sin que se tengan que escribir de nuevo.