SQL Server


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

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s