• Artículo

Diseñar procedimientos almacenados (motor de base de datos)

Casi cualquier código Transact-SQL que puede escribirse como un lote puede utilizarse para crear un procedimiento almacenado.

Reglas para diseñar procedimientos almacenados

Entre las reglas para diseñar procedimientos almacenados se incluyen las siguientes:

  • La propia definición de CREATE PROCEDURE puede incluir cualquier número y tipo de instrucciones SQL, excepto las indicadas a continuación. No pueden utilizarse en ninguna parte de un procedimiento almacenado.

    CREATE AGGREGATE

    CREATE RULE

    CREATE DEFAULT

    CREATE SCHEMA

    CREATE o ALTER FUNCTION

    CREATE o ALTER TRIGGER

    CREATE o ALTER PROCEDURE

    CREATE o ALTER VIEW

    SET PARSEONLY

    SET SHOWPLAN_ALL

    SET SHOWPLAN_TEXT

    SET SHOWPLAN_XML

    USE database_name

     

  • Puede crear otros objetos de base de datos dentro de un procedimiento almacenado. Puede hacer referencia a un objeto creado en el mismo procedimiento almacenado, siempre que se haya creado antes de hacer referencia a él.

  • Puede hacer referencia a tablas temporales dentro de un procedimiento almacenado.

  • Si crea una tabla temporal local dentro de un procedimiento almacenado, ésta existirá únicamente para los fines del procedimiento y desaparecerá cuando éste finalice.

  • Si ejecuta un procedimiento almacenado que llama a otro procedimiento almacenado, este último puede tener acceso a todos los objetos creados por el primero, incluidas las tablas temporales.

  • Si ejecuta un procedimiento almacenado remoto que realiza cambios en una instancia remota de Microsoft SQL Server, los cambios no se pueden revertir.Los procedimientos almacenados remotos no intervienen en las transacciones.

  • El número máximo de parámetros en un procedimiento almacenado es de 2100.

  • El número máximo de variables locales en un procedimiento almacenado está limitado únicamente por la memoria disponible.

  • En función de la memoria disponible, el tamaño máximo de un procedimiento almacenado es de 128 megabytes (MB).

Calificar nombres dentro de procedimientos almacenados

Dentro de un procedimiento almacenado, los nombres de los objetos utilizados en instrucciones (por ejemplo, SELECT o INSERT), que no se califican por esquema adoptan el valor predeterminado del esquema del procedimiento en cuestión. Si un usuario que crea un procedimiento almacenado no califica el nombre de las tablas o las vistas a las que se hace referencia en las instrucciones SELECT, INSERT, UPDATE o DELETE dentro del mismo procedimiento, el acceso a esas tablas a través del procedimiento se restringe, de forma predeterminada, al creador de este último.

Si otros usuarios van a utilizar el procedimiento almacenado, los nombres de objetos utilizados con las instrucciones de lenguaje de definición de datos (DDL) como instrucciones CREATE, ALTER o DROP, instrucciones DBCC, instrucciones EXECUTE y SQL dinámicas, deben calificarse con el nombre del esquema de objeto. Si se especifica el nombre de esquema en estos objetos, se garantiza que el nombre indica el mismo objeto, independientemente de quién llama al procedimiento almacenado. Si no se especifica un nombre de esquema, SQL Server intenta resolver el nombre del objeto utilizando en primer lugar el esquema predeterminado del usuario que realiza la llamada o del usuario especificado en la cláusula EXECUTE AS y, en segundo lugar, el esquema de dbo.

Ofuscamiento de definiciones de procedimientos

Para convertir el texto original de la instrucción CREATE PROCEDURE a un formato ofuscado, utilice la opción WITH ENCRYPTION. El resultado de la ofuscación no puede verse directamente en ninguna de las tablas o vistas del sistema en SQL Server 2008: los usuarios sin acceso a las tablas del sistema, vistas de sistemas o archivos de base de datos no pueden recuperar el texto confuso. Sin embargo, el texto está disponible a los usuarios privilegiados con acceso directo a los archivos de base de datos. Estos usuarios quizá puedan aplicar ingeniería inversa a la ofuscación para recuperar el texto original de la definición de los procedimientos almacenados.

Opciones de la instrucción SET

Motor de base de datos guarda los valores de SET QUOTED_IDENTIFIER y de SET ANSI_NULLS cuando se crea o se altera un procedimiento almacenado Transact-SQL. Estos valores originales se utilizan cuando se ejecuta el procedimiento almacenado. Por tanto, cualquier valor de sesión de cliente de SET QUOTED_IDENTIFIER y SET ANSI_NULLS se omitirá durante la ejecución del procedimiento almacenado. Las instrucciones SET QUOTED_IDENTIFIER y SET ANSI_NULLS que se producen en el procedimiento almacenado no afectan a la funcionalidad del mismo.

Otras opciones de SET, como SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS no se guardan cuando se crea o se altera un procedimiento almacenado. Si la lógica del procedimiento almacenado depende de un valor específico, incluya una instrucción SET al inicio del procedimiento para asegurar el valor adecuado. Cuando una instrucción SET se ejecuta desde un procedimiento almacenado, el valor permanece activo sólo hasta que se completa el procedimiento almacenado. A continuación, la configuración vuelve al valor que tenía cuando se llamó al procedimiento almacenado. Esto permite a los clientes individuales establecer las opciones deseadas sin afectar a la lógica del procedimiento almacenado.

Nota

No se respeta ANSI_WARNINGS al pasar parámetros de un procedimiento almacenado o de una función definida por el usuario o al declarar y establecer variables en una instrucción de lote. Por ejemplo, si una variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan hasta el tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.