lunes, 29 de febrero de 2016

Procedimientos almacenados en SQL Server - Parte I

Buenos días queridos lectores, el día de hoy como les comentaba en el post anterior, veremos los procedimientos almacenados en SQL SERVER. Veamos un poco de teoría antes de pasar a la práctica, aunque como siempre, la práctica es la que nos hará aprender.



Procedimientos almacenados

Un procedimiento almacenado es un conjunto de instrucciones de Transact-SQL que puede aceptar y devolver parámetros proporcionados por el usuario.

Los procedimientos se pueden crear para uso permanente o para uso temporal en una sesión, un procedimiento local temporal, o para su uso temporal en todas las sesiones, un procedimiento temporal global.

El motor de base de datos considera al procedimiento almacenado como un objeto que contiene un conjunto de instrucciones SQL que se ejecutan en conjunto con un mismo nombre y una sola llamada.

Consideraciones generales sobre los procedimientos almacenados

  • Todo procedimiento almacenado se registra en el servidor actual
  • Pueden incluir atributos de seguridad como permisos y cadenas de propiedad; además se les pueden asociar certificados.
  • Los procedimientos almacenados mejoran la seguridad de una aplicación
  • Los procedimientos almacenados con parámetros pueden ayudar a proteger la aplicación ante ataques por inyecciones de códifo SQL
  • Permiten una programación modular
  • Puede crear el procedimiento una vez y llamarlo desde el programa tantas veces lo desee. Así puede mejorar el mantenimiento de la aplicación y permitir que las aplicaciones tengan acceso a la base de datos de manera uniforme
  • Pueden reducir el tráfico de red.
  • Una operación que necesite centenares de líneas de código puede realizarse mediante una sola instrucción que ejecute el código en un procedimiento, en vez de enviar cientos de líneas por código a la red.

Tipos de procedimientos almacenados

Se describen particularmente tres tipos de procedimientos almacenados:
  • Procedimientos almacenados del sistema.
  • Procedimientos almacenados definidos por el usuario.
  • Procedimientos almacenados Extendidos

Procedimientos Almacenados del Sistema

Los procedimientos almacenados del sistema, generalmente son guardados en la base de datos MASTER y son típicamente identificados por el prefijo SP_. Ellos realizan una amplia variedad de tareas para soportar las funciones del SQL Server soportando:
  • Llamadas de aplicaciones externas para datos de las tablas del sistema
  • Procedimientos generales para la administración de la base de datos.
  • Funciones de administración de seguridad.
Nombraremos algunos procedimientos almacenados del sistema y veremos algunos ejemplos relevantes de estos procedimientos:
  • sp_catalogos
  • sp_column_provileges
  • sp_column_privileges_ex
  • sp_columns
  • sp_columns_ex
  • sp_databases
  • sp_executeSQL
  • sp_datatype_info
  • sp_fkeys
  • sp_foreignkeys
  • sp_indexes
  • sp_pkeys
  • sp_primarykeys
  • sp_server_info
  • sp_special_columns
  • sp_sproc_columns
  • sp_statistics
  • sp_table_privileges
  • sp_table_privileges_ex
  • sp_tables
  • sp_tables_ex.
Ahora veremos algunos ejemplos con algunos procedimientos almacenados del sistema

Ejemplo 1:
Procedimiento almacenado del sistema que muestra los privilegios de las columnas involucradas en la tabla PASAJERO

SP_COLUMN_PRIVILEGES PASAJERO
GO

La imagen siguiente muestra el resultado de la ejecución el procedimiento almacenado.


Como notarán queridos lectores, se muestran todas las columnas de la tabla PASAJEROen COLUMN_NAME, además de los privilegios que cada columna especificado en PRIVILEGE.

Ejemplo 2:
Procedimiento almacenado del sistema que muestre las columnas de la tabla PASAJERO

SP_COLUMNS PASAJERO
GO

En el script se ejecuta el procedimiento almacenado del sistema que permitirá mostrar las columnas de la tabla PASAJERO.


Ejemplo 3:
Procedimiento almacenado del sistema que muestre las bases de datos del servidor activo.

SP_DATABASES
GO

Este procedimiento tiene la capacidad de mostrar todas las bases de datos del servidor, además del tamaño asignado a cada una.


Ejemplo 4:
Procedimiento almacenado del sistema, que muestre el pasajero de código IDPASAJERO=P0001 de la tabla PASAJERO



EXECUTE SP_EXECUTESQL
       N'SELECT * FROM PASAJERO
       WHERE IDPASAJERO=@COD',
       N'@COD CHAR(5)',@COD='P0001'
GO

En el script se ejecuta el procedimiento almacenado EXECUTESQL que tiene como tipo de dato de devolución a nTEXT, es por eso que la consulta debe estar encerrada entre "N".

La siguiente imagen muestra el resultado de la ejecución del procedimiento almcenado:


Ejemplo 5:
Procedimiento almacenado que devuelva información sobre el servidor de SQL SERVER

SP_SERVER_INFO

GO

El resultado de la ejecución se muestra en la siguiente imagen:


Ejemplo 6:
Procedimiento almacenado del sistema que devuelve una lista de todos los índices y estadísticas de la tabla PASAJERO

SP_STATISTICS PASAJERO
GO

En la siguiente imagen se muestra el resultado de la ejecución:



Ejemplo 7:
Procedimiento almacenado del sistema que devuelva la lista de permisos de tabla (como INSERT, DELETE, UPDATE, SELECT o REFERENCES) para la tabla AEROPUERTO



EXECUTE SP_TABLE_PRIVILEGES 'AEROPUERTO'
GO

En el script se permiten mostrar los privilegios asignados a la tabla AEROPUERTO como lo muestra la siguiente imagen:

Ejemplo 8:
Procedimiento almacenado del sistema que devuelva las tablas de la base de datos AGENCIA.

USE AGENCIA
GO

SP_TABLES
GO

En el script se permiten mostrar los privilegios asignados a la tabla AEROPUERTO como lo muestra la siguiente imagen:


Como notará, este procedimiento almacenado del sistema, no solo muestra las tablas definidas por el usuario sino también las vistas y las tablas nativas de la base de datos.

Instrucción EXECUTE y SP_EXECUTE


La instrucción EXECUTE indica que SqlClient, ODBC, OLE DB o DB-Library debe ejecutar una o varias instrucciones Transact-SQL preparadas, claro está que dichas instrucciones deben cumplir con la sintaxis evaluada desde el motor de base de datos de SQL

El SP_EXECUTE es un procedimiento almacenado, que ejecuta una cadena Unicode en la cual admite la sustitución de parámetros. Considere que el valor deberá ser mínimamente de tipo NVARCHAR, este procedimiento deberá ejecutarse con la instrucción EXEC obligatoriamente.

Veamos dos ejemplos que explicarán el uso de la instrucción EXEC y el procedimiento almacenado SP_EXECUTESQL

DECLARE @X VARCHAR(MAX)
SET @X='SELECT * FROM PASAJERO'
EXEC (@X)
GO

En el sript se usa la variable @X como contenedor de una consulta simple sobre la tabla PASAJERO, luego la instrucción SET asigna esa consulta a la variable y finalmente se muestra el resultado de la consulta por medio de la instrucción EXEC que tiene como parámetro la variable @X.

DECLARE @X NVARCHAR(MAX)
SET @X='SELECT * FROM PASAJERO'
EXEC SP_EXECUTESQL @X
GO

En el sript se cambia de tipo de dato de la variable @N por NVARCHAR y se le asigna el mismo valor de la consulta para que lo ejecute el procedimiento almacenado SP_EXECUTESQL con el parámetro @X.

En la imagen siguiente se muestra el resultado de ambos scripts

Hasta aquí el post de la primera parte de los procedimientos almacenados, mejor conocidos como SP, en el próximo post hablaré de los procedimientos almacenados creados por el usurio.

Les dejo Procedimientos Almacenados Parte II para que no pierdan el hilo del ejercicio.

Espero que les haya sido de utilidad, también que compartan, comenten, le den +1 o algun clic en algún anuncio que les interese.




Banderas de Visitantes

Flag Counter