09
Oct
09

Transact Sql Básico – Stored Procedures (SP)

Bueno, dando continuidad al Transact Sql, me gustaría comentar sobre otro importante tema como son los Stored Procedures o Procedimientos almacenados.

Un procedimiento almacenado es un bloque de código de que se ejecuta dentro del motor de la base de datos con el fin de realizar tareas dentro de la misma base y en consecuencia, evitar que el usuario tenga que ver con dichas tareas; Esto se puede aplicar tanto a procesos delicados como la actualización o modificación de muchos registros a la vez, y también a procesos sencillos como un select o un delete, la diversidad puede ser muy extensa ya que son herramientas muy potentes del motor de base de datos.

Quizá la función mas importante y el mayor beneficio que podemos sacar de los SP es que se puede limitar o incluso evitar que el usuario tenga acceso directo a las tablas de nuestra base de datos, haciendo que las tareas de consulta las realicen los SP mediante parametros que podamos pasarles

Veamos como hacer un sencillo SP desde el Analizador de Consultas de nuestro SQL Server…

Create Procedure SP_EliminarEstudiante @PId_Estudiante varchar(20) As
Delete From Estudiantes
Where Id_Estudiante = @PId_Estudiante
go

Explico el código:

Create es la palabra reservada en Sql para crear un nuevo elemento, una base, una tabla, un SP, etc.

Con Procedure le decimos que lo que estamos creando es un Procedimiento Almacenado

lo siguiente es escribir el nombre de nuestro procedimiento: SP_EliminarEstudiante, (Por lo del SP_  he de recordar la entrada sobre Nomenclaruta Hungara, para no confundirnos)

Al SP le pasaremos un parámetro, el numero de la cuenta que eliminaremos, los parametos para los SP se declaran con @P, seguidos por el nombre del parámetro y su tipo de dato: @PId_Estudiante VarChar (20) si pasaremos más de un parámetro los separaremos por comas…

Al final colocamos la palabra AS

Después de esto, colocamos las instrucciones que queremos que nuestro SP ejecute cuando sea llamado…

Delete From Estudiantes
Where Estudiante = @PId_Estudiante

(Ojo, que aqui es donde utilizamos los parámetros que llamamos)

y al final, colocamos la célebre palabra Go.

Esto es básicamente un SP, ejecutando lo que le queramos y lo único que hay que hacer es llamarlo por una sencilla linea en nuestro Analizador de Consultas:

SP_EliminarEstudiante ‘CFPDM01’

(el nombre de nuestro SP y los parámetros que le hayamos declarado)…

Cabe mencionar que el poder del SP reside en el alto grado de complejidad que puede adoptar, ya que es capaz de controlar estructuras de manejo de flujo de datos, IF THEN ELSE, entre otros: Aqui pongo un ejemplo de un SP mas complejo:
Create Procedure SP_ObtenerEstudiante @PId_Estudiante varchar(50), @PFull int As
If
@PId_Estudiante is null and @PFull = 0
Select Id_Estudiante, Nombres, Apellidos
From Estudiantes
Order By Id_Estudiante
else if
@PId_Estudiante is not null and @PFull = 0
Select Id_Estudiante, Nombres, Apellidos
From Estudiantes
Where IdEstudiante =
@PId_Estudiante
Order By Id_Estudiante
else if
@PId_Estudiante is null and @PFull = 1
Select *
From Estudiantes
Order By Id_Estudiante
else if
@PId_Estudiante is not null and @PFull = 1
Select *
From Estudiantes
Where Id_Estudiante = @PId_Estudiante
Order By Id_Estudiante
Go

El procedimiento anterior lleva a cabo una seleccion de los posiles valores de los dos parametros requeridos, evaluando cada caso y dando una cadena de consulta distinta para cada uno…

Nota Importante: Cuando se asignan parámetros a los SP, es posible evaluar no solo los valores que contienen, sino también, si contienen o no valores; Tal es el caso del procedimiento anterior que verifica si el parámetro @PId_Estudiante es nulo o no. Por lo que las posibilidades con los SP que se nos vengan en mente son ilimitadas…

Saludos…


4 Responses to “Transact Sql Básico – Stored Procedures (SP)”


  1. 1 Belen
    octubre 18, 2009 a las 2:09 pm

    En tu codigo del SP esxribiste

    If
    @PId_Estudiante is null and @PFull = 0
    Select Id_Estudiante, Nombres, Apellidos
    From Estudiantes
    Order By Id_Estudiante

    Pero con eso, encuentra todos los estudiantes que tengan un ID nulo, asi que ¿Como podria mostrarlos todo por Id?

    • 2 Axl_505
      octubre 19, 2009 a las 8:21 am

      Ok Belen, te explico…

      El el SP se piden dos parámetros, el @PId_Estudiante y @PFull, el primero lo utilizaremos para saber si se quiere la información de un estudiante espedífico o de todos los estudianes registrados, de ahí el proceso de evaluación del IF en el SP

      If
      @PId_Estudiante is null and @PFull = 0 <—————- aqui evalua que si el parametro es nulo osea que no lleva valor, haga lo siguiente:
      Select Id_Estudiante, Nombres, Apellidos
      From Estudiante
      Order By Id_Estudiante

      Como nuestro parámetro de comparación "Id_Estudiante" es nulo y no tendremos con que comparar, no le ponemos instrucción de condición Where, porque no nos devolvería nada al comparar con un valor nulo, y como no lleva Where devolverá todas las filas de la tabla ordenadas por la columna Id_Estudiante.

      Mira la siguiente evaluación del IF, donde @PId_Estudiante no es nulo, entonces si ponemos Where porque si tenemos con que comparar…
      pruébalo en el sql con el script de la base de datos de la entrada anterior de Transact SQL Básico, verás que haciendolo funcionar es más fácil comprenderlo…

      Saludos Belen y gracias por tu comentario…

  2. 3 Alex
    octubre 28, 2009 a las 12:30 am

    Tengo una pregunta,
    quiero hacer un sp que su compilacion me bote el primer registro de una tabla y al compilarlo de nuevo me bote el segundo y asi susecivamente hasta que cuando llege al ultimo se repita el primero…

    esto es por cada compilada , compilo una vez para el primero, vuelbo a compilar para el segundo , vuelbo a compilar para el tercero y asi susecivamente.

    espero que ma ayudes…
    alex

    saludos

    • 4 Axl_505
      octubre 29, 2009 a las 11:45 am

      mmmmmmmmmmmmmmmm……..

      mira, segun te entiendo, dos formas de resolver tu problema serían: 1.- que manejes una variable en memoria que se acumule y pase como parámetro a tu SP, lo que solo te funcinaría si el SP lo llamas desde una aplicación. 2- en caso que lo hagas a pata, un SP que en haga una tabla y cuando termine de recorrer todos los registros la borre, me inventé este SP con el que podes empezar, solo le chequeé la sintaxis pero en teoría te debería servir de algo o para que tu lo modifiques a tu gusto

      Create Procedure Contador As
      –declara dos variables que ocupa en los if
      declare @Val int
      declare @val2 int
      set @Val = (Select count(registro) from tabla)
      If Exists (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘TablaTemp’)
      –si la tabla existe te da el registro que corresponde y le incrementa en uno para que a la siguiente vez que te lo llame vaya incrementando
      –si la temporal ya tiene el valor del total de registros, la borra
      begin
      select tabla.registro from tabla inner join TablaTemp on tablatemp.campo_1 > 0 where tabla.n_registro = tablatemp.n_registro + 1
      update tablatemp set campo_1 = campo_1 + 1
      set @val2 = (select count(campo_1) from tablatemp)
      if @val2 = @val
      begin
      exec(‘drop table tablatemp’)
      end
      end
      –si no existe la crea y le asigna el valor uno para que te devuelva el primer registro
      else
      begin
      exec(‘Create Table TablaTemp
      (
      campo_1 int
      )
      go’)
      insert into catalogo (campo_1) values(1)
      select tabla.registro from tabla inner join TablaTemp on tablatemp.campo_1 > 0 where tabla.n_registro = tablatemp.n_registro + 1
      update tablatemp set campo_1 = campo_1 + 1
      end

      espero que te sirva…. Saludos.

      Axl_505


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


A %d blogueros les gusta esto: