Procedimientos Almacenados
Un procedimiento almacenado son instrucciones T-SQL almacenadas con un nombre en la base de datos.
Los procedimientos almacenados se pueden utilizar para
- Devolver un conjunto de resultados, se puede incluir parámetros de entrada para especificar el filtro del conjunto resultado.
- Ejecutar instrucciones de programación.
- Devolver valores numéricos que permiten realizar acciones cuando un grupo de instrucciones se realizó con éxito o no.
Ventajas del uso de procedimientos almacenados
Reutilización del código
El encapsulamiento en un procedimiento es óptimo para reutilizar su código. Se elimina la necesidad de escribir el mismo código, se reducen inconsistencias en el código y permite que cualquier usuario ejecute el código aún sin tener acceso a los objetos que hace referencia.
Mayor seguridad
Se pueden ejecutar SP con instrucciones que hacen referencia a objetos que los usuarios no tienen permisos. El procedimiento realiza la ejecución del código y todas las instrucciones y controla el acceso a los objetos a los que hace referencia. Esto hace mas sencillo la asignación de permisos. Se puede implementar la suplantación de usuarios usando Exexute As. Existe un nivel fuerte de encapsulamiento.
Tráfico de red reducido
Un SP se ejecuta en un único lote de código. Esto reduce el tráfico de red cliente servidor porque únicamente se envía a través de la red la llamada que ejecuta el SP. La encapsulación del código del SP permite que viaje a través de la red como un solo bloque.
Mantenimiento más sencillo
Se puede trabajar en los aplicativos en base a capas, cualquier cambio en la Base de datos, hace sencillo los cambios en los procedimientos que hacen uso de los objetos cambiados en la BD.
Rendimiento mejorado
Los procedimientos almacenados se compila la primera vez que se ejecutan y crean un plan de ejecución que vuelve a usarse en posteriores ejecuciones.
Tipos de procedimientos
Definidos por el usuario
Se crea por el usuario en las bases de datos definidas por el usuario o en las de sistema (Master, Tempdb, Model y MSDB)
Procedimientos almacenados Temporales
Los procedimientos temporales son procedimientos definidos por el usuario, estos se almacenan en tempdb. Existen dos tipos de procedimientos temporales: locales (primer caracter es #) y globales (primer caracter ##). Se diferencian entre sí por los nombres, la visibilidad y la disponibilidad.
Los procedimientos temporales locales tienen como primer carácter de sus nombres un solo signo de número (#); solo son visibles en la conexión actual del usuario y se eliminan cuando se cierra la conexión. Los procedimientos temporales globales presentan dos signos de número (##) antes del nombre; lo pueden usar todos los usuarios conectados, se eliminan cuando se desconectan todos los usuarios.
Procedimientos Almacenados del Sistema
Los procedimientos del sistema son propios de SQL Server. Los caracteres iniciales de estos procedimientos son sp_ la cual no se recomienda para los procedimientos almacenados definidos por el usuario.
Extendidos definidos por el usuario
Los procedimientos extendidos tienen instrucciones externas en un lenguaje de programación como puede ser C. Estos procedimientos almacenados son DLL que una instancia de SQL Server puede cargar y ejecutar dinámicamente.
Sintaxis
Para crear un procedimiento almacenado se utiliza
Create procedure NombreProcedimiento
(
@PrimerParametro TipoDato,
@SegundoParametro TipoDato,…
)
As
Instrucciones del SP
go
Para modificar un SP
Alter procedure NombreProcedimiento
(
@PrimerParametro TipoDato,
@SegundoParametro TipoDato,… cambios
)
As
Instrucciones del SP con cambios
go
Eliminar un SP
Drop procedure NombreProcedimiento
go
Para listar los SP
select * from sys.procedures
go
Ejercicios
Use Northwind
go
— Procedimiento para listar los productos
Create procedure spProductosListadoPrecios
As
Select P.ProductID, P.ProductName,
P.UnitPrice , P.UnitsInStock
from Products As P
go
Ejecutar el Store Procedure creado
Execute spProductosListadoPrecios
go
— Procedimiento para insertar un registro en la tabla Shippers
— La instrucción para insertar un Shipper es:
insert into Shippers (CompanyName, Phone)
values (‘Tolva Couriers’,’954542452′)
go
El SP para insertar Shippers se crea de la siguiente forma.
create procedure spShippersInsertaNuevo
(
@NombreEmpresa nvarchar(40),
@Fono nvarchar(24)
)
As
insert into Shippers (CompanyName, Phone)
values (@NombreEmpresa,@Fono)
go
Ejecutar el SP, se puede ejecutar de las siguiente formas:
Execute spShippersInsertaNuevo ‘Chasqui’,’87545852′
go
Execute spShippersInsertaNuevo
@Fono = ‘345435645’, @NombreEmpresa =’Ford’
go
Execute spShippersInsertaNuevo
@NombreEmpresa =’Turbo XD’, @Fono = ‘8569856’
go
Procedimiento para el listado de productos de una determinada categoría
Create procedure spProductosListadoPorCategoria
(
@CategoriaCodigo int
)
As
select P.ProductID, P.ProductName,
P.UnitPrice , P.UnitsInStock, P.UnitsOnOrder
from Products As P
where CategoryID = @CategoriaCodigo
go
Ejecutar el SP
Productos de categoria 2
Execute spProductosListadoPorCategoria 2
go
Modificar el procedimiento de Listado de Productos por categoría que lo muestre ordenados por precio descendente
Alter procedure spProductosListadoPorCategoria
(
@CategoriaCodigo int
)
As
select P.ProductID, P.ProductName,
P.UnitPrice , P.UnitsInStock, P.UnitsOnOrder
from Products As P
where CategoryID = @CategoriaCodigo
order by P.UnitPrice desc
go
— Procedimiento para crear una tabla con los productos de una determinada categoría
Create procedure spCreaTablaProductosDeCategoria
(
@CodigoCategoria int
)
As
Declare @NombreTabla nvarchar(40), @DropTablaTSQL nvarchar(100),@CrearTablaTSQL nvarchar(100)
set @NombreTabla = ‘ProductosDeCategoria’+ LTRIM(STR(@CodigoCategoria))
Set @DropTablaTSQL = ‘Drop Table ‘+ @NombreTabla
Set @CrearTablaTSQL = ‘select * into ‘+ @NombreTabla + ‘ from Products where CategoryID = ‘+LTRIM(STR(@CodigoCategoria))
if exists (select * from sys.tables where name = @NombreTabla)
Begin
Execute(@DropTablaTSQL)
End
Execute(@CrearTablaTSQL)
go
Ejecutar para la categoría 3
Execute spCreaTablaProductosDeCategoria 3
go
Ver los registros
select * from Productosdecategoria3
go