Crear procedimiento almacenados sql server
En este artículo estaremos
hablando de procedimiento almacenado en sql, muy útiles en la administración de base
de datos para ejecutar conjuntos de instrucciones, aprenderemos a cómo crearlos
y las combinaciones que podemos hacer.
Índice
Definicion y caracteristicas de un procedimiento almacenado
Los procedimientos almacenados o Store
Procedure son un conjunto de comandos almacenados de forma física en
una base de datos, los cuales no necesitan ser nuevamente ejecutados de manera
individual, pero pueden ejecutarse de manera automática atravez del
procedimiento almacenado. La implementación de estos varía de un gestor de
base de datos a otro.
Los procedimientos almacenados permiten que
los usuarios tengan un acceso más fácil a los registros, sin tener
que conocer a detalle la estructura de la tabla. Lo procedimiento almacenados
nos permiten definir datos de entradas, declarar variables locales y mostrar
registros almacenados en una tabla.
También nos ofrecen potencia ya que nos
permite ejecutar instrucciones y operaciones complejas en pocos pasos
definidos de tal manera que cumplan el objetivo.
Hay 2 formas de crear el procedimiento almacenado,
la primera podemos crearlos median Transact-SQL y la segunda
podemos crearlos atravez del uso del asistente del SQL Management
Studio.
Existen tres tipos de procedimientos almacenados:
Procedimientos almacenados definidos por el
usuario: este tipo de store procedure se crear en la base de datos y contiene
instrucciones DML (INSERT, UPDATE, DELETE, SELECT).
Procedimiento almacenados del sistema: Estos
son pertenecientes al sistema de SQL Server, se encuentran
almacenados en la base de datos interna del sistema y podemos verlo de forma
lógica en sys.schemas.
Procedimiento almacenados Temporales: estos tipos
de procedimientos son definidos por el usuario y se dividen en dos: Locales que
los podemos definir con el símbolo “#” y lo Globales que son
definidos por dos caracteres “##”.
El tema de procedimientos almacenado es bastante extenso y complejo dada su importancia e implementación, vamos a crear una serie de ejemplo lo más fáciles posibles para que se comprenda bien el tema.
Creacion de procedimiento almacenado mediante Transact-SQL
Mediante
Transact-SQL podemos crear, actualizar y eliminar Procedimientos almacenados. Para
crear un procedimiento almacenado debemos hacer uso de la sentencia CREATE PROCEDURE , si necesitamos
modificar alguno de los parámetros definidos en el procedimiento almacenado debemos
usar la sentencia ALTER PROCEDURE, y por ultimo si vamos a eliminar un
procedimiento almacenado que no estemos utilizando podemos hacerlo mediante la sentencia
DROP PROCEDURE.
Mediante el
uso de procedimientos almacenados podemos Insertar, Actualizar, Consultar y Eliminar registros de una tabla lo que los hace una herramienta poderosa al
momento del desarrollo de una aplicación.
La sintaxis
para crear un procedimiento almacenado mediante Transact-SQL es la siguiente:
CREATE PROCEDURE NOMBREPROCEDIMIENTO @NOMBREPARAMETRO TIPO_DATO AS SENTENCIAS SQL(INSERT,UPDATE,SELECT, DELETE); GO
Mediante el uso de esta sintaxis podemos crear un procedimiento almacenado, cabe mencionar un punto importante ya que también al crear un procedimiento almacenado podemos hacer combinaciones de sentencias es decir como ejemplo supongamos que deseamos insertar los registros en una tabla x y que esa información se me actualice en otra tabla y , mediante procedimientos almacenados podemos crear bloques de instrucciones que nos permitan realizar operaciones un poco mas complejas. Para poder llevar a cabo esto debemos usar las palabras claves BEGIN - END las cuales nos van a permitir separar las operaciones a realizar dentro del procedimiento almacenado.
CREATE PROCEDURE NOMBREPROCEDIMIENTO @NOMBREPARAMETRO TIPO_DATO AS --Primera sentencia a ejecutar BEGIN SENTENCIAS SQL(INSERT,UPDATE,SELECT, DELETE); END --Segunda sentencia a ejecutar BEGIN SENTENCIAS SQL(INSERT,UPDATE,SELECT, DELETE); END GO
Ahora ya vimos la sintaxis para crear un procedimiento almacenado, pero
se preguntarán cómo funciona, expliquemos la sintaxis rápidamente
CREATE PROCEDURE: es la sentencia que define que vamos a crear un
procedimiento almacenado
NOMBREPROCEDIMIENTO: acá definimos el nombre por el cual vamos a identificar
el procedimiento almacenado por lo general en mi caso siempre uso una palabra
inicial para diferenciar como es “sp”.
@NOMBREPARAMETRO: acá definimos el parámetro que va intervenir en las
operaciones dentro del procedimiento almacenado.
TIPO_DATO: acá especificamos el tipo dato que vamos a definir al parámetro
del procedimiento almacenado, el cual puede ser un entero, un tipo fecha, o un carácter.
AS: la palabra clave AS la utilizamos para decir que vamos a abrir una instrucción.
SENTENCIAS SQL: aquí es donde definimos el tipo de operación a realizar
si vamos a realizar un guardado de registros (Insert), una modificación de registros
(Update), una selección de registros (Select) o una eliminación de registros (Delete).
GO: nos indica el cierre de una instrucción creada en el procedimiento
almacenado.
BEGIN-END: con estas palabras estamos indicando el inicio y el final de un bloque de operaciones dentro de un procedimiento almacenado.
Creacion de procedimiento almacenado INSERT
Ahora ya explicado la sintaxis y lo que podemos hacer con un procedimiento almacenado vamos a utilizar una base de datos llamada "DBtutorial" y una tabla llamada "Autos" para crear una serie de ejemplos.
Creemos un procedimiento almacenado para insertar los registros en la tabla “Autos”.
CREATE PROCEDURE spInsertarAuto
@Codigo INT,
@Nombre VARCHAR(100),
@Marca VARCHAR(100),
@Año INT,
@Fecha DATETIME
AS
--DECLARAMOS LA SENTENCIA INSERT
INSERT INTO Autos(Codigo,Nombre,Marca,Año,FechaEstreno)
VALUES(@Codigo,@Nombre,@Marca,@Año,@Fecha)
GO Ya hemos creado el procedimiento almacenado para insertar los registros en la tabla autos, si observamos hemos declarado los parámetros en base a los campos que la tabla contiene así como sus tipos de datos, si por algún motivo nosotros declaramos un tipo de datos diferente en uno de los parámetros el procedimiento almacenado se va crear pero no se va a ejecutar ya que el tipo de datos definido en el parámetro no coincide con el de la tabla.
ahora creemos el procedimiento almacenado en SQL Management Studio.
Si queremos ubicar en donde se guardan los procedimientos almacenado creado nos podemos ir al explorador de objetos, nos situamos sobre nuestra base de datos y buscamos la opción que diga programmabality, desplegamos la opción Stored Procedures y nos va a mostrar los procedimientos almacenado creados.
para ejecutar el procedimiento almacenado creado debemos utilizar la palabra clave EXEC, seguido de los parámetros definidos.
Al ejecutar el procedimiento almacenado debemos escribir los valores de cada parámetro definido, como vemos vamos a insertar registros de un nuevo vehículo, como la tabla ya contenía registros pues vamos a seguir a partir del codigo 8 en adelante, si realizamos un select posterior ala ejecución del procedimiento almacenado podremos observar si se insertaron los registros.
Creacion de procedimiento almacenado UPDATE
CREATE PROCEDURE spActualizarAuto @Nombre VARCHAR(100), @Marca VARCHAR(100), @Año INT, @Fecha DATETIME AS --DECLARAMOS LA SENTENCIA UPDATE UPDATE Autos SET Nombre = @Nombre, Marca = @Marca, Año = @Año, FechaEstreno = @Fecha GO
Veamos hemos declarado el procedimiento almacenado spActualizarAuto, cuando nosotros ejecutemos este procedimiento almacenado lo que va a ocurrir es que todo los registros de la tabla se van a actualizar al mismo nombre de auto, marcar, esto debido a que no hay un filtro que nos especifique que registros es el que necesitamos actualizar. Por eso es necesario hacer uso de la clausula WHERE para evitarnos estos tipos de errores. Otra observación importante al momento de querer actualizar registros de llaves primarias o secundarias atravez de un procedimiento almacenado este nos enviara un error al momento de ejecutarlo ya que son campos únicos que no pueden ser modificados en la tabla.
Entonces creemos el procedimiento almacenado para actualizar los registros de la tabla autos. pero que esta actualización se realice por el ID de auto.
CREATE PROCEDURE spActualizarAuto @ID int, @Nombre VARCHAR(100), @Marca VARCHAR(100), @Año INT, @Fecha DATETIME AS --DECLARAMOS LA SENTENCIA UPDATE UPDATE Autos SET Nombre = @Nombre, Marca = @Marca, Año = @Año, FechaEstreno = @Fecha WHERE Codigo = @ID GO
Ahora si nuestro procedimiento almacenado contiene un filtro así que el update solamente afectara al registro del cual le especifiquemos el ID. Guardemos el procedimiento en SQL Management Studio.
Ahora ejecutemos el Procedimiento spActualizar auto y modifiquemos la fecha de estreno y marca al auto de ID 8.
Hagamos un SELECT para verificar si se aplico el cambio en la fecha y la marca.
Y si se aplico el cambio ala fecha y la marca del auto con ID 8.
Creacion de procedimiento almacenado SELECT
Acontinuaciion vamos a crear un procedimiento almacenado para seleccionar todo los registros de la tabla, también para la selección si necesitáramos filtrar algunos registros bien podemos hacerlo utilizando la clausula WHERE, pero para este ejemplo vamos a seleccionar todo los registros.
CREATE PROCEDURE spSeleccionarAutos AS --SENTENCIA SELECT SELECT * FROM Autos GO
El procedimiento para seleccionar los registros es a ojos muy sencillo pero podemos personalizarlos y especificar cada uno de los campos de la tabla. guardemos el procedimiento almacenado en el Management Studio.
Ahora ejecutemos el procedimiento almacenado spSeleccionarAutos.
Como podemos Observar el procedimiento almacenado spSelecionarAutos nos va a mostrar todo los registros de autos existentes en la tabla.
Creacion de procedimiento almacenado DELETE
Ahora vamos a crear un procedimiento almacenado para eliminar los registros de una tabla. Acá hay que explicar algo importante que siempre me gustar recalcar al igual que al realizar un UPDATE al realizar un DELETE es importante utilizar filtro utilizando la clausula WHERE ya que sino hacemos esto podemos provocar la perdida completa de todo los registros de la tabla, por lo cual muy pocas veces es utilizado DELETE dentro de un procedimiento almacenado a menos que necesitemos quitar algunos registros dependiendo del tipo de operación que realicemos.
Ahora bien creemos un procedimiento almacenado en el que eliminemos los registros de la tabla autos por el ID.
CREATE PROCEDURE spEliminarAutos @Codigo INT AS DELETE FROM Autos WHERE Codigo = @Codigo GO
Creemos el procedimiento almacenado spEliminarAutos en SQL Management Studio:
Ahora ejecutemos el procedimiento almacenado spEliminarAutos y elimimenos el auto con ID 8.
Se elimino el auto con el ID 8, ejecutemos el procedimiento almacenado spSeleccionarAutos para verificar si ya no existe el ID 8.
Verificamos y el registro ya no existe en la tabla. Delete podemos usarla siempre y cuando tengamos en cuenta especificar bien que es lo que vamos a eliminar sino nos puede generar daños irreversibles. En otro articulo estaremos hablando sobre los respaldo de base de datos para cuando nos ocurran percances por haber ejecutado una mala operación.
Creacion de procedimiento almacenado para bloque de sentencias
Ahora ya vimos como ejecutar procedimiento almacenado para cada sentencia ahora veremos un ejemplo de como ejecutar bloques de sentencias. Para eso vamos a insertar registros en la tabla "Autos" pero además necesitamos que esa información se registre en una tabla "Repuestos", creemos el procedimiento almacenado.
CREATE PROCEDURE spInsertarAutoRepuesto
@Codigo INT,
@Nombre VARCHAR(100),
@Marca VARCHAR(100),
@Año INT,
@Fecha DATETIME
AS
BEGIN
--1..DECLARAMOS LA SENTENCIA INSERT EN LA TABLA AUTO
INSERT INTO Autos(Codigo,Nombre,Marca,Año,FechaEstreno)
VALUES(@Codigo,@Nombre,@Marca,@Año,@Fecha)
END
--2..DECLARAMOS LA SENTENCIA INSERT EN LA TABLA REPUESTOS
BEGIN
INSERT INTO AutosRepuestos(AutoID,Marca,Año)
VALUES(@Codigo,@Marca,@Año)
END
GOLo que estamos haciendo en este procedimiento almacenado es registrar la información de un auto y ala misma vez que ese registro del auto se almacene en la tabla de Repuestos, guardemos el procedimiento almacenado en el Management Studio:
Ahora ejecutemos el procedimiento almacenado con los registros de un auto:
Hagamos un SELECT a la tabla AUTOS y AUTOREPUESTOS para verificar si los registros se insertaron en ambas tablas.
Modificacion de un procedimiento almacenado
Para modificar un procedimiento almacenado debemos utilizar la sentencia ALTER PROCEDURE. Supongamos que necesitamos modificar el procedimiento almacenado spActualizarAutos en el cual vamos a quitar el parámetro fecha y vamos a obtener la fecha del día utilizando la función GETDATE() para obtener la fecha del día, veamos el procedimiento almacenado.
ALTER PROCEDURE spActualizarAuto @ID int, @Nombre VARCHAR(100), @Marca VARCHAR(100), @Año INT AS --DECLARAMOS LA SENTENCIA UPDATE UPDATE Autos SET Nombre = @Nombre, Marca = @Marca, Año = @Año, FechaEstreno = GETDATE() WHERE Codigo = @ID GO
ahora vamos a crear el procedimiento almacenado en Management Studio de SQL para aplicar los cambios realizados:
Eliminar un procedimiento almacenado
Por ultimo si necesitamos eliminar un procedimiento almacenado debemos utilizar la sentencia DROP PROCEDURE. Supongamos que necesitamos eliminar el procedimiento almacenado spEliminarAutos, la sentencia seria la siguiente:
Ejecutemos la sentencia en SQL Management Studio:
Se procedió a eliminar el procedimiento almacenado de la base de datos autos.
En este articulo hemos estudiado a fondo los procedimientos almacenados, como crearlos y ejecutarlos. Los procedimiento almacenados tienen un gran peso de utilidad cuando desarrollamos aplicaciones que se conectan ala base de datos. espero que el articulo les sea de gran utilidad.
0 Comentarios