Procedimientos Almacenados SQL

Los procedimientos almacenados SQL son conjuntos de instrucciones SQL que se guardan en la base de datos y se pueden ejecutar de forma repetida. Estos procedimientos típicamente realizan tareas comunes, como consultas complejas, actualizaciones de datos o validaciones de datos. Los procedimientos almacenados se pueden ejecutar desde una aplicación cliente o desde otro procedimiento almacenado. Esto permite una mayor modularidad y reutilización de código en la base de datos.

También podemos decir que los procedimientos almacenados SQL guardan una colección de una o un grupo de declaraciones guardadas en el servidor SQL. Aquí, explicamos cómo crear, cambiar el nombre, modificar y eliminar procedimientos almacenados en SQL Server con ejemplos. Los siguientes son los beneficios de usarlos.

• Todos los procedimientos almacenados SQL están pre compilados y su plan de ejecución se almacena en caché. Entonces, cuando ejecute el mismo nuevamente, usará el caché.
• Le ayudará a encapsular las reglas y políticas comerciales. Por ejemplo, el administrador de la base de datos creará un procedimiento y varios usuarios accederán a él desde JAVA, C#, Python, R, etc.
• En lugar de enviar cientos de líneas de código, es mejor usar un procedimiento almacenado para que podamos llamar a la declaración única (su nombre) en lugar de escribir la declaración compleja o enviarla a través de la red.
• Los procedimientos almacenados del servidor SQL lo ayudan a acceder a los objetos de la base de datos de una manera más segura y uniforme.
• Nos ayudan a la conservación del ancho de banda de la red (proteger contra daños o retrasos)

Le sugiero que consulte los siguientes ejemplos para comprender los procedimientos almacenados en detalle:

• SELECT para entender, cómo escribir las declaraciones SELECT en los procedimientos almacenados SQL
• INSERTAR artículo para seguir los pasos para escribir las declaraciones INSERT en su interior.
• ACTUALIZAR para escribir las declaraciones de ACTUALIZACIÓN dentro de él.
• Tablas temporales: cree procedimientos temporales que incluyan el procedimiento temporal local o el procedimiento temporal global.

Antes de entrar en el ejemplo práctico, veamos la sintaxis.

Sintaxis para crear procedimientos almacenados en SQL 

La sintaxis básica para crear procedimientos almacenados SQL Server se muestra a continuación:

CREATE [OR ALTER] PROCEDURE [Schema_Name].Procedure_Name
                @Parametro_Nombre Tipo_Datos, 
                .... 
                @Parametro_Nombre Tipo_Datos, 
AS
   BEGIN
      -- Query
      -- SELECT, Insert, Update, O Delete declaraciones
     
   END

Pero, primero, déjame explicarte la sintaxis.

• Schema_name: especifique el nombre del esquema. Por ejemplo, dbo
• Procedimiento_Nombre: puede especificar cualquier nombre de procedimientos almacenados SQL que desee dar que no sean las palabras clave reservadas del sistema. Intente utilizar nombres significativos para que pueda identificarlos rápidamente.
• @Parameter_Nombre: cada sp acepta cero o más parámetros, según los requisitos del usuario. Al declarar los parámetros, no olvide el tipo de datos apropiado. Por ejemplo (@Nombre_completo VARCHAR(50), @Id_codigo INT)

vista la sintaxis para crear un procedimiento almacenado en SQL, ahora pasaremos a la acción.

Crear procedimientos almacenados mediante Query

Cuando queremos crear un procedimiento almacenado mediante una consulta SQL, use la declaración CREATE PROCEDURE de la siguiente manera: Este ejemplo muestra cómo crear procedimientos almacenados SQL utilizando la instrucción Create procedure y una consulta select.

CREATE PROCEDURE SPLista_pedido
AS
BEGIN

SELECT OrderID
,Fecha
,ClienteID
,Moneda
,Costo
FROM ejemplo.dbo.Pedidos
ORDER BY ClienteID

END

Para compilar este procedimiento almacenado, lo ejecuta como una instrucción SQL en el Management Studio, como se muestra en la imagen anterior.

• El spLista_pedido es el nombre del procedimiento almacenado.
• La palabra clave AS  separa el encabezado y el cuerpo del procedimiento almacenado.
• Si el procedimiento almacenado tiene una declaración, las palabras clave BEGIN y END que rodean la declaración son opcionales. Sin embargo, es una buena práctica incluirlos para que el código quede claro.

Puede sustituir la palabra clave CREATE PROCEDURE, por CREATE PROC para acortar la declaración. Si el cambio es valido, aparecerá un mensaje: «Los comandos se han completado correctamente». Significa que el procedimiento almacenado SQL server, se ha compilado y guardado correctamente.

El procedimiento almacenado creando anteriormente, se encuentra en el Explorador de objetos, base de datos Ejemplo, en la carpeta programación> Procedimientos almacenados, esto lo puede ver en la siguiente imagen:

Algunas veces, es bueno que hagas clic en el botón refrescar en el explorador de objetos, para poder ver los últimos cambios.

Ejecutando un procedimiento almacenado

Para ejecutar un procedimiento almacenado, utiliza la instrucción EXECUTE o EXEC seguida del nombre del procedimiento almacenado:

EXECUTE sp_name;

donde sp_namees el nombre del procedimiento almacenado que desea ejecutar.
Por ejemplo, para ejecutar el SPLista_pedido procedimiento almacenado, utilice la siguiente declaración:

EXEC SPLista_pedido;

El procedimiento almacenado devuelve el siguiente resultado:

Modificar un procedimiento almacenado

Para modificar procedimientos almacenados SQL existente, utilice la declaración ALTER PROCEDURE. Primero, abra el procedimiento almacenado para ver su contenido haciendo clic con el botón derecho en el nombre del procedimiento almacenado y seleccione el elemento de menú Modificar :

En segundo lugar, cambie el cuerpo del procedimiento almacenado. En tercer lugar, haga clic en el botón Ejecutar, SQL Server modifica el procedimiento almacenado.

Los comandos se han completado correctamente. Ahora, si vuelve a ejecutar el procedimiento almacenado, verá que los cambios surten efecto:

EXEC SPLista_pedido;

Eliminar procedimiento almacenado

Para eliminar procedimientos almacenados SQL, utilice la instrucción DROP PROCEDURE o DROP PROC:

DROP PROCEDURE sp_name;

o

DROP PROC sp_name;

donde sp_nameestá el nombre del procedimiento almacenado que desea eliminar. Por ejemplo, para eliminar el procedimiento almacenado SPLista_pedido, ejecute la siguiente instrucción:

DROP PROCEDURE SPLista_pedido

Eliminar usando Management Studio

En este ejemplo, mostraremos cómo eliminarlo procedimientos almacenados SQL mediante SSMS o Management Studio. Para hacerlo, navegue hasta el que desea eliminar. A continuación, haga clic derecho en el nombre para abrir el menú contextual. Finalmente, puede hacer clic en la opción Eliminar, como se muestra a continuación.

Para esta demostración, vamos a eliminar el procedimiento almacenado creado anteriormente. Una vez que seleccione la opción de eliminar, se abrirá una ventana Eliminar objeto, como se muestra a continuación. A donde va hacer clic en el botón Mostrar dependencias para verificar las dependencias y luego haga clic en Aceptar para eliminarlas.

Consejos al utilizar procedimientos almacenados SQL

Podemos mejorar los procedimientos almacenas SQL de la siguiente manera:

• En los procedimientos almacenados SQL Intente reemplazar el operador UNION o el operador OR con el operador UNION ALL a menos que esté buscando valores distintos.
• Si es posible, evite usar las funciones ESCALAR en la instrucción SELECT que devuelve una gran cantidad de datos. Esto se debe a que la función escalar se aplica en cada fila (fila básica), lo que afecta el rendimiento de la consulta.
• Para manejar los errores, SQL nos permite usar la función TRY CATCH  dentro de los Procedimientos almacenados SQL. Entonces, intente usar la función TRY CATCH.
• Utilice siempre BEGIN..COMMIT  TRANSACTION  dentro del SP. Recuerda que la transacción debe ser lo más breve posible. De lo contrario, existe el peligro de un interbloqueo o un bloqueo prolongado.
• Intente usar los nombres de esquema mientras crea o hace referencia a cualquier objeto de base de datos. Disminuirá el tiempo de procesamiento del motor de la base de datos.
• Siempre especifique los nombres de las columnas requeridas dentro de la instrucción SELECT. Y evite la instrucción SELECT *.
• Al crear o modificar una tabla usando (CREATE TABLE o ALTER TABLE), use la palabra clave DEFAULT para asignar los valores predeterminados a las columnas. Evitará los valores NULL y asignará esos valores predeterminados a los datos de la columna.
• Al crear tablas temporales dentro del sp, debe especificar si la columna acepta NULLS o NO explícitamente. Se puede hacer usando NULL o NOT NULL.
• En lugar de extraer o insertar una gran cantidad de datos, intente trabajar con menos datos esenciales. Reduce la carga de procesamiento de consultas y aumenta el rendimiento de las consultas.
• Utilice la declaración SET NOCOUNT ON dentro del procedimiento almacenado del servidor SQL. Desactivará los mensajes que SQL Server envía al Cliente. Incluye el número de filas actualizadas, eliminadas, etc.

En este tutorial, aprendió cómo administrar los procedimientos almacenados de SQL Server, incluida la creación, ejecución, modificación y eliminación de procedimientos almacenados SQL.