sábado, 23 de mayo de 2015

Procedimientos almacenados con parametros en SQL Server



Sintaxis:

Create Procedure Procedimiento @NombreParametro Tipo de dato As
Instrucciones

Posteriormente lo ejecutamos introduciendo un valor/res correspondiente al parámetro/os introducido.

Exec Procedimiento Valor


Ejemplo 1

CREATE PROCEDURE NUMEMPDEPT @NDEP SMALLINT
AS SELECT COUNT(*) AS [NUM EMPLEADOS], DEPT_NO
FROM EMP
WHERE DEPT_NO = @NDEP
GROUP BY DEPT_NO

Crea el procedimiento numempdept con el parámetro ndep que es tipo smallint (equivalente al short en visual).
El procedimiento hace una select que realiza grupos por número de deparamento y cuenta cuantos empleados hay en cada departamento.

EXEC NUMEMPDEPT 20

Ejecuta el procedimiento introduciendo el valor 20 para el parámetro ndep, con lo que haría la select sobre el departamento 20.


Ejemplo 2

CREATE PROCEDURE BUSCAEMP(
@NDEP SMALLINT,
@NOFICIO NVARCHAR(19))
AS
SELECT * FROM EMP
WHERE DEPT_NO = @NDEP
AND OFICIO = @NOFICIO

EXEC BUSCAEMP 20,'Empleado'



PROCEDIMIENTOS ALMACENADOS CON PARÁMETROS CON VALORES POR DEFECTO

Sintaxis:

Create Procedure Nombre @Variable tipo = Valor
As
Instrucciones

Donde Valor es el valor que le damos por defecto, este valor puede almacenar comodines (como % que equivale a *).


Ejemplo 1

Crea un procedimiento con el parámetro ndept y le da por defecto valor 10.

CREATE PROCEDURE NUMEMP @NDEPT SMALLINT = 10
AS
SELECT DEPT_NO, COUNT(*) AS [NUM EMPLEADOS]
FROM EMP
WHERE DEPT_NO = @NDEPT
GROUP BY DEPT_NO

EXEC NUMEMP



Ejemplo 2

Debe devolver salario, oficio y comisión y le pasamos el apellido.

CREATE PROCEDURE SALARIOOFICIO @PAPELLIDO VARCHAR(20) = 'REY' AS
SELECT OFICIO, SALARIO, COMISION FROM EMP WHERE APELLIDO = @PAPELLIDO

EXEC SALARIOOFICIO 'GARCIA'

Sacaría el salario, el oficio y la comisión de todos los que tengan apellido Garcia, sino pusiésemos parámetro, por defecto sacaría los que tuviesen apellido Rey.


Ejemplo 3

CREATE PROCEDURE SALARIOOFICIOLIKE(
@PAPELLIDO VARCHAR(20) = 'REY' )
AS
SELECT OFICIO, SALARIO, COMISION
FROM EMP
WHERE APELLIDO LIKE '%' + @PAPELLIDO + '%'

EXEC SALARIOOFICIOLIKE 's'

Sacaría oficio, salario y comisión de los empleados que tuviesen una s en su apellido.


Ejemplo 4

Introducir oficio y salario debe sacar el apellido de los empleados que tengan el mismo apellido y ganen mas del salario indicado. Debemos hacer que sino introduce nada saque todos los registros.

CREATE PROCEDURE DOSPARAMETROS2(
@OFICIO VARCHAR(12) = '%',
@SALARIO VARCHAR(10) = '0' )
AS
SELECT APELLIDO FROM EMP WHERE OFICIO LIKE @OFICIO AND SALARIO > @SALARIO

EXEC DOSPARAMETROS2 'Empleado',1000



Ejemplo 5

Sacar todos los empleados que se dieron de alta entre una determinada fecha inicial y fecha final y que pertenecen a un determinado departamento.

CREATE PROCEDURE TRESPARAMETROS(
@FINICIAL DATETIME = '01-01-1980',
@FFINAL SMALLDATETIME = '12-07-2002' ,
@DEPT_NO NVARCHAR(10) = '%'
)
AS
SELECT * FROM EMP WHERE FECHA_ALT BETWEEN @FINICIAL AND @FFINAL
AND CAST(DEPT_NO AS VARCHAR(10)) LIKE @DEPT_NO

EXEC TRESPARAMETROS '01-01-1980','12-07-2002','30'



Ejemplo 6

Crear procedimiento que inserte un empleado. Crear otro procedimiento que borre un empleado que coincida con los parámetros indicados (los parámetros serán todos los campos de la tabla empleado)

CREATE PROCEDURE [INSERTA EMPLEADO](
@EMP_NO INT,
@APELLIDO NVARCHAR(20),
@OFICIO NVARCHAR(20),
@DIR INT,
@FECHA_ALT SMALLDATETIME,
@SALARIO INT,
@COMISION INT,
@DEPT_NO INT)
AS
INSERT INTO EMP VALUES
(@EMP_NO,
@APELLIDO,
@OFICIO,
@DIR,
@FECHA_ALT,
@SALARIO,
@COMISION,
@DEPT_NO)

EXEC [INSERTA EMPLEADO] 7855,'ALIAGA','EMPLEADO',7782,'23/05/2015',45000,0,30



CREATE PROCEDURE [BORRA EMPLEADO](
@EMP_NO INT,
@APELLIDO NVARCHAR(20),
@OFICIO NVARCHAR(20),
@DIR INT,
@FECHA_ALT SMALLDATETIME,
@SALARIO INT,
@COMISION INT,
@DEPT_NO INT
)
AS
DELETE FROM EMP WHERE EMP_NO = @EMP_NO
AND APELLIDO = @APELLIDO
AND OFICIO = @OFICIO
AND DIR = @DIR
AND FECHA_ALT = @FECHA_ALT
AND SALARIO = @SALARIO
AND COMISION = @COMISION
AND DEPT_NO = @DEPT_NO

EXEC [BORRA EMPLEADO] 7855,'ALIAGA','EMPLEADO',7782,'23/05/2015',45000,0,30



Espero haber ayudado en algo. Hasta la próxima oportunidad!








6 comentarios:

  1. ¿Por qué usas el COUNT(*) para contar y es recomendable usar el select * asi se trate de un ejemplo?

    ResponderEliminar
    Respuestas
    1. Hola! Anónimo, gracias por la visita y el aporte de tu comentario!
      Uso Count(*) porque necesito obtener la cantidad de "todos" los empleados que pertenecen a un departamento.

      Los mejores deseos! Hasta cualquier instante!

      Eliminar
    2. tal cual!!!
      estan buenisimos los tutoriales!!! estaria bueno si haces algunos con las diferentes conecciones de bd con los diferentes lenguajes!!!

      Eliminar
    3. Hola Anónimo, gracias por la visita y el aporte de tu comentario!.
      Espero realizarlo!!
      Éxitos!! Hasta cualquier instante!!

      Eliminar
  2. Hola Guillermo Benitez, he visto tus ejemplos y se me hace muy claros y practico, yo tengo el problema que en un sitio web necesito saber cuantos parametros recibe un procedimiento, ya que tengo casi 500 procedimiento y todos diferentes y necesito saber cuantos parametros recibe antes de ejecutarlo, sabes si hay alguna sentencia sql que me diga el numero de paramentros de un procedimiento??

    ResponderEliminar
    Respuestas
    1. Hola Armando Polledo, gracias por la visita y el aporte de tu comentario!
      Sinceramente nunca me había planteado esa pregunta o he tenido la necesidad de obtener el número de parámetros de un procedimiento y más aún desde una sentencia SQL.
      Me agradaría también saber si es posible. Ojalá algún visitante nos pueda compartir el conocimiento!

      Los mejores deseos! Hasta cualquier momento!

      Eliminar