domingo, 24 de mayo de 2015

Variables en procedimientos almacenados de SQL Server



Sintaxis de declaración
Declare @nombre tipo_dato

Sintaxis de asignación directa
Set @nombre = valor

Sintaxis de asignación mediante consulta
Select @nombre = campo from tabla


VARIABLES DE SALIDA EN PROCEDIMIENTOS ALMACENADOS

Sintaxis

Create Procedure Nombre @Variable tipodedato Output
Instrucciones
Print @Variable


Ejemplo 1:

CREATE PROCEDURE TOTALSALCOM @APELLIDO NVARCHAR(25), @TOTAL INT OUTPUT
--CREAMOS EL PROCEDIMIENTO CON UN PARÁMETRO Y UNA VARIABLE DE SALIDA
AS
--DECLARAMOS DOS VARIABLES PARA ALMACENAR VALORES
DECLARE @SAL INT
DECLARE @COM INT
--ASIGNAMOS LOS VALORES CORRESPONDIENTES A LAS VARIABLES Y DESPUÉS LAS SUMAMOS Y LAS GUARDAMOS EN LA VARIABLE DE SALIDA.
SELECT @SAL = SALARIO FROM EMP WHERE APELLIDO = @APELLIDO
SELECT @COM = COMISION FROM EMP WHERE APELLIDO = @APELLIDO
SET @TOTAL = @SAL + @COM
--DEVOLVEMOS EL VALOR DE LA VARIABLE QUE QUEREMOS
PRINT @TOTAL

Y para ejecutarlo en el analizador de consultas, hemos de declarar la variable que vamos a recuperar también, para almacenar el valor que nos pasa el procedimiento almacenado en ella. Es decir, en este caso @total almacenará el valor pasado por la variable del mismo nombre que hay en el procedimiento:

DECLARE @TOTAL INT
EXEC TOTALSALCOM 'JIMENEZ', @TOTAL OUTPUT



Ejemplo 2:

CREATE PROCEDURE TOTALES
@NDEPT INT = NULL, @TOTAL INT OUTPUT
AS
IF @NDEPT IS NULL
SELECT @TOTAL = SUM(SALARIO) FROM EMP
ELSE
SELECT @TOTAL = SUM(SALARIO) FROM EMP WHERE DEPT_NO = @NDEPT
SELECT @TOTAL

DECLARE @TOTAL INT
EXEC TOTALES 10, @TOTAL OUTPUT



MAS EJEMPLOS:

CREATE PROCEDURE DOSPARAMETROS(
@OFICIO VARCHAR(12) = '%',
@SALARIO INT = 0 )
AS
IF @SALARIO = 0
PRINT 'NO HA INTRODUCIDO SALARIO'
ELSE
SELECT APELLIDO FROM EMP WHERE OFICIO LIKE @OFICIO AND SALARIO > @SALARIO

EXEC DOSPARAMETROS 'EMPLEADO',1000



CREATE TABLE [CONTROL]
(
APELLIDO VARCHAR(45),
USUARIO VARCHAR(45),
FECHA SMALLDATETIME,
NUMERO INT
)
GO
CREATE PROCEDURE BORRAREMP (
@APELLIDO VARCHAR(12) = '0000',
@APE NVARCHAR(30) = '0',
@NUM INT = 0)
AS
IF @APELLIDO = '0000'
PRINT 'IMPOSIBLE BORRAR SI NO INTRODUCE UN APELLIDO'
ELSE
BEGIN
SELECT @APE = APELLIDO FROM EMP WHERE APELLIDO = @APELLIDO
SELECT @NUM = COUNT(APELLIDO) FROM EMP WHERE APELLIDO = @APE
IF (@APE = '0')
BEGIN
PRINT 'APELLIDO NO ENCONTRADO, INTRODUZCA UN APELLIDO DE LA BASE DE DATOS'
END
ELSE
BEGIN
INSERT INTO CONTROL VALUES
(@APE
,USER_NAME()
,GETDATE()
,@NUM)
PRINT 'EL EMPLEADO ' + @APE + ' HA SIDO BORRADO DE LA BASE DE DATOS'
DELETE FROM EMP WHERE APELLIDO = @APE
END
END

EXEC BORRAREMP 'ALONSO','', 0



--ELIMINAR EL PROCEDIMIENTO ALMACENADO
DROP PROCEDURE BORRAREMP



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








4 comentarios:

  1. Muy buen día,
    Seria genial si dejas el backup de la base de datos para realizar la practica de tus ejercicios. De igual gracias por dedicar tu tiempo para que otras personas aprendan.

    ResponderEliminar
    Respuestas
    1. Hola Nestor Molano, gracias por la visita y el aporte de tu comentario.
      Estos procedimientos son sólo de ejemplo, sin embargo los ejercicios que realizamos en esta serie de tutoriales son de la base de datos diseñada para dichos ejercicios, cuyos script de creación y llenado de datos están en el índice.

      Los mejores deseos! Hasta cualquier momento!

      Eliminar
  2. Como puedo hacer si queiro hacer una consulta, con un usuario que solo tiene para consultar tablas, pero quiero almacenar con un select la variable, pero al llamar la tabla prinicipal esta me devuelve un valor para todas. Ejemplo:
    Estoy llamando la tabla AGH004 esta contiene monto_liq,rutas, cod_coc, pero para esto al cod_coc = 51, y lo asigo a la variable @valor = monto_liq con la condicion ya mencionada, pero cuando mando a llamar la tabla select rutas, @valor from AGH004 esta me trae las rutas pero con un mismo valor 1366 y no el valor por cada una.

    ResponderEliminar
    Respuestas
    1. Hola Hector Choez Ortiz, gracias por la visita y el aporte de tu consulta.

      Tengo algunas hipótesis pero me gustaría que compartas el código que elaboraste para ayudarte y entenderlo mejor. Una de mis hipótesis es que se está quedando con el monto_liq del cod_coc = 51.

      Espero tu código. :)

      Éxitos! Hasta cualquier momento.

      Eliminar