PROCEDIMIENTOS ALMACENADOS
Los procedimientos almacenados (PA) son módulos o rutinas que encapsulan código para su reutilización.
Los PA son similares a los procedimientos de otros lenguajes de programación.
– Aceptan parámetros de entrada.
– Devuelven varios valores.
– Contienen instrucciones de programación que realizan operaciones en la base de datos, incluidas las llamadas a otros procedimientos.
– Devuelven un valor de estado a un lote o a un procedimiento que realiza una llamada para indicar si la operación se ha realizado correctamente o se han producido errores.
– Devuelve los motivos de los errores.
Ventajas de usar procedimientos almacenados.
– Se registran en el servidor.
– Pueden incluir atributos de seguridad, se pueden asociar certificados.
– Mejoran la seguridad de la aplicación. Inyección de código SQL.
– Permite una programación Modular.
– Mejora el mantenimiento y acceso a la base de datos de manera uniforme.
– Reduce el tráfico de red.
Tipos de procedimientos almacenados
– Procedimientos almacenados definidos por el usuario.
o Transact-SQL. Un procedimiento almacenado Transact-SQL es una colección guardada de instrucciones Transact-SQL que puede tomar y devolver los parámetros proporcionados por el usuario.
o CLR. Un procedimiento almacenado CLR es una referencia a un método Common Language Runtime (CLR) de Microsoft .NET Framework que puede aceptar y devolver parámetros suministrados por el usuario.
– Procedimientos almacenados extendidos.
o Se eliminarán en futuras versiones.
o Los procedimientos almacenados extendidos le permiten crear sus propias rutinas externas en un lenguaje de programación como pueda ser C.
o Se programan con la API Procedimiento almacenado extendido de SQL Server.
– Procedimientos almacenados del sistema.
o Muchas de las actividades administrativas en SQL Server se realizan mediante un tipo especial de procedimiento conocido como procedimiento almacenado del sistema.
o Los procedimientos almacenados del sistema aparecen de forma lógica en el esquema sys de cada base de datos definida por el usuario y el sistema.
Reglas para diseñar procedimientos almacenados.
o La propia definición de CREATE PROCEDURE puede incluir cualquier número y tipo de instrucciones SQL.
o Puede crear otros objetos de base de datos dentro de un procedimiento almacenado.
o Puede hacer referencia a tablas temporales.
o Si crea una tabla temporal local dentro de un procedimiento almacenado, ésta existirá únicamente para los fines del procedimiento y desaparecerá cuando éste finalice.
o 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.
o El número máximo de parámetros en un procedimiento almacenado es de 2100.
o El número máximo de variables locales en un procedimiento almacenado está limitado únicamente por la memoria disponible.
o En función de la memoria disponible, el tamaño máximo de un procedimiento almacenado es de 128 megabytes (MB).
Crear procedimientos almacenados.
Operadores Básicos
– Create Procedure. Crear un procedimiento almacenado
– Alter Procedure. Modifica un procedimiento almacenado
– Output. Define un parámetro como “de salida”
– Set. Permite asignar un valor a un parámetro o variable.
– Declare. Permite declarar variables que serán utilizadas en el cuerpo del procedimiento almacenado
– Begin Tran. Inicia una transacción, esta instrucción puede llevar un nombre
– Commit tran. Finaliza y confirma una transacción
– If Else. Permite realizar decisiones en el cuerpo del procedimiento almacenado
– Begin. Inicia un conjunto de sentencias sql
– End. Finaliza el conjunto de sentencias sql.
– If @@Error <> 0. si no es igual a 0 significa que se ha producido un error, al momento de de insertar, actualizar o eliminar.
– Rollback Transaction. Elimina las operaciones que se han realizado en una transacción. Esta instrucción tiene que estar antes del comm tran.
– @@Identity. Devuelve el último valor insertado de un campo autoincrementable.
Ejemplos.
Crear un procedimiento almacenado para insertar un Empleado.
CREATE PROCEDURE dbo.Actividades_I_Empleado
@ApellidoPaterno Varchar(35),
@ApellidoMaterno Varchar(35),
@Nombre1 Varchar(35),
@Nombre2 Varchar(35)
AS
Insert into Empleado (ApellidoPaterno, ApellidoMaterno, Nombre1, Nombre2)
values (@ApellidoPaterno, @ApellidoMaterno, @Nombre1, @Nombre2)
ALTER PROCEDURE [dbo].[Actividades_I_Empleado]
@ApellidoPaterno Varchar(35),
@ApellidoMaterno Varchar(35),
@Nombre1 Varchar(35),
@Nombre2 Varchar(35)
AS
Begin
Insert into Empleado (ApellidoPaterno, ApellidoMaterno, Nombre1, Nombre2)
values (@ApellidoPaterno, @ApellidoMaterno, @Nombre1, @Nombre2)
Select @@IDENTITY
End
La sentencia ALTER PROCUDURE, permite modificar el procedimiento almacenado.
Crear un procedimiento almacenado para insertar una actividad.
CREATE PROCEDURE I_ActividadDiaria
@CodigoTopico int,
@FechaInicio datetime,
@FechaFinalizacion datetime,
@Estado char(1),
@Id_Empleado int
AS
begin
insert into ActividadDiaria(CodigoTopico, FechaInicio, FechaFinalizacion, Estado, Id_Empleado)
Values (@CodigoTopico, @FechaInicio, @FechaFinalizacion, @Estado, @Id_Empleado)
end
Crear un procedimiento almacenado que seleccione a todos los empleados de un grupo.
CREATE PROCEDURE S_EmpleadosDeUnGrupo
@NombreGrupo varchar(50)
AS
begin
Select a.ApellidoPaterno, a.ApellidoMaterno, a.Nombre1, a.Nombre2
From Empleado a inner join GrupoEmpleado b on a.Id_Empleado=b.Id_empleado
inner join Grupo c on b.CodigoGrupo=c.CodigoGrupo
Where c.NombreGrupo=@NombreGrupo
end
Crear un procedimiento almacenado que permita insertar una cuenta de empleado.
CREATE PROCEDURE I_CuentaEmpleado
@CuentaEmpleado varchar(20),
@Id_Empleado int
AS
begin
Insert into CuentaEmpleado (CuentaEmpleado, Id_empleado)
Values (@CuentaEmpleado, @Id_Empleado)
end
Crear un procedimiento para insertar a un empleado y asignar un grupo.
CREATE PROCEDURE I_EmpleadoAsignarGrupo
@ApellidoPaterno varchar(35),
@ApellidoMaterno varchar(35),
@Nombre1 varchar(35),
@Nombre2 varchar(35),
@NombreGrupo varchar(50)
AS
begin tran
DECLARE @Id_Empleado int
DECLARE @CodigoGrupo int
INSERT INTO Empleado(ApellidoPaterno, ApellidoMaterno, Nombre1, Nombre2)
VALUES(@ApellidoPaterno, @ApellidoMaterno, @Nombre1, @Nombre2)
SET @Id_Empleado = @@IDENTITY
SELECT @CodigoGrupo=CodigoGrupo FROM Grupo WHERE NombreGrupo=@NombreGrupo
if @CodigoGrupo>0
begin
INSERT INTO GrupoEmpleado(CodigoGrupo, Id_Empleado)
VALUES(@CodigoGrupo, @Id_Empleado)
print N’Se Inserto’
end
else
print N’ocurrio un error’
if @@error<>0
ROLLBACK TRANSACTION
Commit
Crear un procedimiento almacenado que replique a una tabla auxiliar los datos siguientes. CodigoActividad, NombreCompletoEmpleado, NombreTopico, Estado.
create procedure G_BolcarDatos
as
begin tran
CREATE TABLE Auxiliar
(
CodigoActividad int,
NombreEmpleado varchar(105),
NombreTopico varchar(35),
Estado char(1)
)
Insert into Auxiliar SELECT ActividadDiaria.CodigoActividad,
Empleado.ApellidoPaterno+’ ‘+Empleado.ApellidoMaterno+’ ‘+Empleado.Nombre1+’ ‘+Empleado.Nombre2,
Topico.NombreTopico as Nombre,
ActividadDiaria.Estado
FROM ActividadDiaria INNER JOIN
Empleado ON ActividadDiaria.Id_Empleado = Empleado.Id_Empleado INNER JOIN
Topico ON ActividadDiaria.CodigoTopico = Topico.CodigoTopico
if @@error<>0
ROLLBACK TRANSACTION
commit
Crear un procedimiento almacenado que valide a un empleado.
Create Procedure G_ValidarEmpleado
@CuentaEmpleado varchar(20),
@Id_Empleado int,
@Valido Char(1) OUTPUT
as
BEGIN
declare @CuentaEmp varchar(20)
declare @Id_Emp int
Select @CuentaEmp = CuentaEmpleado, @Id_Emp = Id_Empleado
From CuentaEmpleado
Where CuentaEmpleado=@CuentaEmpleado and Id_Empleado=@Id_Empleado
if @CuentaEmp=@CuentaEmpleado
SET @Valido=’S’
else
SET @Valido=’N’
END