Problemas resueltos sobre Procedimientos almacenados

7/23/2019 Problemas resueltos sobre Procedimientos almacenados

1/21

Todo sobre procedimientos almacenados y triggers

http://www.sqlserverya.com.ar/index.php?inicio=125

) Sacar todos los empleados que se dieron de alta entre una determinada fecha inicial

y fecha final y que pertenecen a un determinado departamento.

?

1

2

!

5

"

#

CREATEPROCEDUREFECHASDEPT

@FINICIAL DATETIME,

@FFINAL SMALLDATETIME ,

@DEPT_NO NVARCHAR(10)

AS

SELECT* FROMEMP WHEREFECHA_ALT BETWEEN@FINICIAL AND@FFINAL

ANDDEPT_NO = @DEPT_NO

?

1 EXECFECHASDEPT '01011!"#','01011!$#',%0

2) Crear procedimiento que inserte un empleado.
http://www.sqlserverya.com.ar/index.php?inicio=125http://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.sqlserverya.com.ar/index.php?inicio=125


2/21

?

1

2

!

5

"

#

$

%

1&

11

12

1

1!

15

1"

1#

1$

1%

CREATEPROCEDUREINSERTA_EMPLEADO

@EMP_NO INT,

@APELLIDO NVARCHAR(%0),

@OFICIO NVARCHAR(%0),

@DIR INT,

@FECHA_ALT SMALLDATETIME,

@SALARIO INT,

@COMISION INT,

@DEPT_NO INT

AS

INSERTINTOEMP VALUES

(@EMP_NO,

@APELLIDO,

@OFICIO,

@DIR,

@FECHA_ALT,

@SALARIO,

@COMISION,

@DEPT_NO)

?

1 EXECINSERTA_EMPLEADO &$!#,'SUARE','EMPLEADO',0,'%"0#%01#',1"000,0,%0
http://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.html


3/21

3) Crear un procedimiento que recupere el nombre, nmero y nmero de personas a

partir del nmero de departamento.

?

1

2

!

5

CREATEPROCEDURENUMEMP_DEPT @NDEP SMALLINT

ASSELECTCOUNT(*) ASNUM EMPLEADOS, DEPT_NO

FROMEMP

WHEREDEPT_NO = @NDEP

ROUPB+DEPT_NO

?

1 EXECNUMEMP_DEPT 0

4) Crear un procedimiento igual que el anterior, pero que recupere tambin las

personas que traba!an en dicho departamento, pas"ndole como par"metro el nombre.


4/21

?

1

2

!

5

"

#

$

%

1&

11

12

1

1!

15

1"

CREATEPROCEDUREPERSONASDEPT @DEPT NVARCHAR(0)

AS

SELECTE-DEPT_NO ASNUMERO DEPT

, D-DNOMBRE ASNOMBRE, COUNT(*) ASNUMERO EMPLEADOS

FROMEMP ASE

INNER.OINDEPT ASD

ONE-DEPT_NO = D-DEPT_NO

WHERED-DNOMBRE = @DEPT

ROUPB+D-DNOMBRE, E-DEPT_NO

SELECTE-DEPT_NO ASN/ DEPARTAMENTO

, D-DNOMBRE ASDEPARTAMENTO

, E-APELLIDO, E-SALARIO

FROMEMP ASE

INNER.OINDEPT ASD



?

1 EXECPERSONASDEPT 'CONTABILIDAD'


5/21

#) Crear procedimiento para de$ol$er salario, oficio y comisi%n, pas"ndole el apellido.

?

1

2

CREATEPROCEDURESALARIO_OFICIO @PAPELLIDO NVARCHAR(%0) AS

SELECTOFICIO, SALARIO, COMISION FROMEMP WHEREAPELLIDO = @PAPELLIDO

?

1 EXECSALARIO_OFICIO 'ARCIA'

&) 'gual que el anterior, pero si no le pasamos ningn $alor, mostrar" los datos de

todos los empleados.


6/21

?

1

2

CREATEPROCEDUREOFICIOSALARIO @PAPELLIDO NVARCHAR(%0) =''AS

SELECTOFICIO, SALARIO, COMISION FROMEMP WHEREAPELLIDO LIE@PAPELLIDO

?

1 EXECOFICIOSALARIO 'SUARE'

() Crear un procedimiento para mostrar el salario, oficio, apellido y nombre del

departamento de todos los empleados que contengan en su apellido el $alor que le

pasemos como par"metro.

?

1

2

CREATEPROCEDURESALARIO_OFICIOLIE @PAPELLIDO VARCHAR(%0) = 'RE+'AS

SELECTOFICIO, SALARIO, COMISION FROMEMP WHEREAPELLIDO LIE''2 @PAPELLIDO 2 '

?

1 EXECSALARIO_OFICIOLIE 'S'

'arte 2
http://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-1-de-2.html


7/21

!ercicios de procedimientos almacenados en S*+

Ser$er 2 de 2

-egresar al ndice

/) Crear un procedimiento que recupere el nmero departamento, el nombre y nmero

de empleados, d"ndole como $alor el nombre del departamento, si el nombre

introducido no es $"lido, mostraremos un mensa!e informati$o comunic"ndolo.

?

1

2

!

5

"

#

$

%

1&

11

12

1

1!

15

1"

1#

1$

CREATEPROCEDUREDEPARTAMENTO @DEPT NVARCHAR(0)

AS

DECLARE@DEPTDEF NVARCHAR(0)

SET@DEPTDEF = NULL

SELECT@DEPTDEF = DNOMBRE FROMDEPT

WHEREDNOMBRE = @DEPT

IF (@DEPTDEF ISNULL)

PRINT 'DEPARTAMENTO INTRODUCIDO NO VALIDO3 '2 @DEPT

ELSE

BEIN

SELECTE-DEPT_NO ASNUMERO DEPT

, D-DNOMBRE ASNOMBRE, COUNT(*) ASNUMERO EMPLEADOS

FROMEMP ASE

INNER.OINDEPT ASD



ROUPB+D-DNOMBRE, E-DEPT_NO

END
http://www.ingenieriasystems.com/2014/01/Manual-de-Microsoft-SQL-Server-Full-Transact-SQL.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-2-de-2.htmlhttp://www.ingenieriasystems.com/2014/01/Manual-de-Microsoft-SQL-Server-Full-Transact-SQL.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-2-de-2.html


8/21

?

1 EXECDEPARTAMENTO 'VENTAS'

0) Crear un procedimiento para de$ol$er un informe sobre los empleados de la plantilla

de un determinado hospital, sala, turno o funci%n. l informe mostrar" nmero de

empleados, media, suma y un informe personali1ado de cada uno que muestre

nmero de empleado, apellido y salario.

?

1

2

!

5

"

#

$

%

1&

11

12

CREATEPROCEDUREEMPPLANTILLA

@VALOR NVARCHAR(0)

AS

DECLARE@CONSULTA NVARCHAR(0)

SELECT@CONSULTA = NOMBRE

FROMHOSPITAL

WHERENOMBRE = @VALOR

IF (@CONSULTA ISNULL)

BEIN

SELECT@CONSULTA = NOMBRE FROMSALA

WHERENOMBRE = @VALOR


BEIN


9/21

1

1!

15

1"

1#

1$

1%

2&

21

22

2

2!

25

2"

2#

2$

2%

&

1

2

!

5

"

#

$

%

!&

SELECT@CONSULTA = T FROMPLANTILLA

WHERET = @VALOR


BEIN

SELECT@CONSULTA = FUNCION FROMPLANTILLA

WHEREFUNCION = @VALOR


BEIN

PRINT 'EL VALOR INTRODUCIDO NO ES UN

HOSPITAL, SALA, TURNO O FUNCI4N3 '2 @VALOR

PRINT 'VERIFI5UE LOS DATOS INTRODUCIDOS'

END

ELSE

BEIN

PRINT 'FUNCION'

SELECTFUNCION ASTURNO

,AV(SALARIO) ASMEDIA

,COUNT(EMPLEADO_NO) ASN/ EMPLEADOS

,SUM(SALARIO) ASSUMA FROMPLANTILLA

ROUPB+FUNCION

HAVINFUNCION = @VALOR

SELECTEMPLEADO_NO ASN/ EMPLEADO

,APELLIDO, SALARIO, FUNCION

FROMPLANTILLA

WHEREFUNCION = @VALOR

END

END

ELSE

BEIN

PRINT 'TURNO'


10/21

!1

!2

!

!!

!5

!"

!#

!$

!%

5&

51

52

5

5!

55

5"

5#

5$

5%

"&

"1

"2

"

"!

"5

""

"#

"$

SELECTT ASTURNO

,AV(SALARIO) ASMEDIA

,COUNT(EMPLEADO_NO) ASN/ EMPLEADOS

,SUM(SALARIO) ASSUMA FROMPLANTILLA

ROUPB+T

HAVINT = @VALOR

SELECTT ASTURNO

,EMPLEADO_NO ASN/ EMPLEADO

,APELLIDO, SALARIO

FROMPLANTILLA

WHERET = @VALOR

END

END

ELSE

BEIN

PRINT 'SALA'

SELECTS-NOMBRE ASSALA

,AV(P-SALARIO) ASMEDIA

,COUNT(P-EMPLEADO_NO) ASN/ EMPLEADOS

,SUM(P-SALARIO) ASSUMA FROMPLANTILLA ASP

INNER.OINSALA ASS

ONS-HOSPITAL_COD = P-HOSPITAL_COD

ROUPB+S-NOMBRE

HAVINS-NOMBRE = @VALOR

SELECTS-NOMBRE ASSALA

,P-EMPLEADO_NO ASN/ EMPLEADO

,P-APELLIDO, P-SALARIO

FROMPLANTILLA ASP

INNER.OINSALA ASS

ONS-HOSPITAL_COD = P-HOSPITAL_COD


11/21

"%

#&

#1

#2

#

#!

#5

#"

##

#$

#%

$&

$1

$2

$

$!

$5

$"

$#

$$

$%

%&

%1

%2

%

%!

%5

WHERES-NOMBRE = @VALOR

END

END

ELSE

BEIN

PRINT 'HOSPITAL'

SELECTH-NOMBRE ASHOSPITAL

,AV(P-SALARIO) ASMEDIA

,COUNT(P-EMPLEADO_NO) ASN/ EMPLEADOS

,SUM(P-SALARIO) ASSUMA FROMPLANTILLA ASP

INNER.OINHOSPITAL ASH

ONH-HOSPITAL_COD = P-HOSPITAL_COD

ROUPB+H-NOMBRE

HAVINH-NOMBRE = @VALOR

SELECTH-NOMBRE ASHOSPITAL

,P-EMPLEADO_NO ASN/ EMPLEADO

,P-APELLIDO, P-SALARIO

FROMPLANTILLA ASP

INNER.OINHOSPITAL ASH

ONH-HOSPITAL_COD = P-HOSPITAL_COD

WHEREH-NOMBRE = @VALOR

END


12/21

?

1 EXECEMPPLANTILLA 'ENERAL'

) Crear un procedimiento en el que pasaremos como par"metro el pellido de un

empleado. l procedimiento de$ol$er" los subordinados del empleado escrito, si el

empleado no e5iste en la base de datos, informaremos de ello, si el empleado no tiene

subordinados, lo informa remos con un mensa!e y mostraremos su !efe. 6ostrar el

nmero de empleado, pellido, 7ficio y 8epartamento de los subordinados.

?

1

2

!

5

"

#

$

%

1&

CREATEPROCEDURE.EFES

@APE NVARCHAR(0)

AS

DECLARE@EMP INT, @.EFE INT, @SUB INT

SELECT@EMP = EMP_NO FROMEMP

WHEREAPELLIDO = @APE

IF (@EMP ISNULL)

BEIN

PRINT 'NO EXISTE NINUN EMPLEADO CON ESTE APELLIDO3 '2 @APE

END

ELSE


13/21

11

12

1

1!

15

1"

1#

1$

1%

2&

21

22

2

2!

25

2"

2#

2$

2%

&

1

2

!

5

"

#

$

BEIN

[email protected] = A-EMP_NO

,@SUB = B-EMP_NO

FROMEMP ASA INNER.OINEMP ASB

ONA-EMP_NO = B-DIR

WHEREB-DIR = @EMP

ORDERB+B-DIR

IF (@.EFE ISNULL)

BEIN

SELECTA-EMP_NO ASN/ DE EMPLEADO

,A-APELLIDO AS.EFE, A-OFICIO

,A-DEPT_NO ASN/ DEPARTAMENTO

,B-EMP_NO ASN/ EMPLEADO

,B-APELLIDO ASSUBORDINADO

,B-OFICIO

,B-DEPT_NO ASN/ DEPARTAMENTO

FROMEMP ASA

INNER.OINEMP ASB

ONB-DIR = A-EMP_NO

WHEREB-EMP_NO = @EMP

ORDERB+B-DIR

END

ELSE

BEIN

SELECTA-EMP_NO ASN/ DE EMPLEADO

,A-APELLIDO AS.EFE, A-OFICIO

,A-DEPT_NO ASN/ DEPARTAMENTO

,B-EMP_NO ASN/ EMPLEADO

,B-APELLIDO ASSUBORDINADO

,B-OFICIO


14/21

%

!&

!1

!2

!

!!

!5

!"

!#

!$

,B-DEPT_NO ASN/ DEPARTAMENTO

FROMEMP ASA INNER.OINEMP ASB

ONA-EMP_NO = B-DIR

WHEREB-DIR = @EMP

ORDERB+B-DIR

END

END

?

1 EXEC.EFES 'ARCIA'

) Crear procedimiento que borre un empleado que coincida con los par"metros

indicados 9los par"metros ser"n todos los campos de la tabla empleado).

?

1

2

!

CREATEPROCEDUREBORRA_EMPLEADO

@EMP_NO INT,

@APELLIDO NVARCHAR(%0),

@OFICIO NVARCHAR(%0),

@DIR INT,


15/21

5

"

#

$

%

1&

11

12

1

1!

15

1"

1#

1$

@FECHA_ALT SMALLDATETIME,

@SALARIO INT,

@COMISION INT,

@DEPT_NO INT

AS

DELETEFROMEMP WHEREEMP_NO = @EMP_NO

ANDAPELLIDO = @APELLIDO

ANDOFICIO = @OFICIO

ANDDIR = @DIR

ANDFECHA_ALT = @FECHA_ALT

ANDSALARIO = @SALARIO

ANDCOMISION = @COMISION


?

1 EXECBORRA_EMPLEADO &$!#,'SUARE','EMPLEADO',0,'%"0#%01#',1"000,0,%0

2) 6odificaci%n del e!ercicio anterior, si no se introducen datos correctamente,

informar de ello con un mensa!e y no reali1ar la ba!a. Si el empleado introducido no

e5iste en la base de datos, deberemos informarlo con un mensa!e que de$uel$a elnombre y nmero de empleado del empleado introducido. Si el empleado e5iste, pero
http://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-2-de-2.htmlhttp://www.ingenieriasystems.com/2015/05/Ejercicios-de-procedimientos-almacenados-en-SQL-Server-2-de-2.html


16/21

los datos para eliminarlo son incorrectos, informaremos mostrando los datos reales del

empleado !unto con los datos introducidos por el usuario, para que se $ea el fallo.

?

1

2

!

5

"

#

$

%

1&

11

12

1

1!

15

1"

1#

1$

1%

2&

21

22

2

2!

CREATEPROCEDUREBORRAEMPLEADO

@EMP_NO INT

,@APELLIDO NVARCHAR(%0)

,@OFICIO NVARCHAR(%0)

,@DIR INT

,@FECHA_ALT SMALLDATETIME

,@SALARIO INT

,@COMISION INT

,@DEPT_NO INT

AS

DECLARE@VALOR NVARCHAR(0)

SELECT@VALOR = EMP_NO

FROMEMP WHEREEMP_NO = @EMP_NO


ANDOFICIO = @OFICIO

ANDDIR = @DIR





IF (@VALOR ISNULL)

BEIN

SELECT@VALOR = EMP_NO FROMEMP WHEREEMP_NO = @EMP_NO

IF (@VALOR ISNULL)

BEIN

PRINT 'EMPLEADO NO EXISTENTE EN LA BASE DE DATOS, VERIFI5UE LOS


17/21

25

2"

2#

2$

2%

&

1

2

!

5

"

#

$

%

!&

!1

!2

!

!!

!5

!"

!#

!$

!%

5&

51

52

DATOS DEL SR '2 @APELLIDO

END

ELSE

BEIN

PRINT 'DATOS INTRODUCIDOS ERRONEAMENTE3 '

PRINT CAST(@EMP_NO ASNVARCHAR(6)) 2 ' '2 @APELLIDO 2 ' '

2 @OFICIO 2 ' '2 CAST(@DIR ASNVARCHAR(6)) 2 ' '

2 CAST(@FECHA_ALT ASNVARCHAR(1%)) 2 ' '

2 CAST(@SALARIO ASNVARCHAR(10)) 2 ' '

2 CAST(@COMISION ASNVARCHAR(10)) 2 ' '

2 CAST(@DEPT_NO ASNVARCHAR(6))

SELECT@EMP_NO = EMP_NO, @APELLIDO = APELLIDO

,@OFICIO = OFICIO, @DIR = DIR

,@FECHA_ALT = FECHA_ALT, @SALARIO = SALARIO

,@COMISION = COMISION, @DEPT_NO = DEPT_NO

FROMEMP WHEREEMP_NO = @VALOR

PRINT 'DATOS REALES DEL EMPLEADO3 '

PRINT CAST(@EMP_NO ASNVARCHAR(6)) 2 ' '2 @APELLIDO 2 ' '

2 @OFICIO 2 ' '2 CAST(@DIR ASNVARCHAR(6)) 2 ' '

2 CAST(@FECHA_ALT ASNVARCHAR(1%)) 2 ' '

2 CAST(@SALARIO ASNVARCHAR(10)) 2 ' '

2 CAST(@COMISION ASNVARCHAR(10)) 2 ' '

2 CAST(@DEPT_NO ASNVARCHAR(6))

END

END

ELSE

BEIN

DELETEFROMEMP WHEREEMP_NO = @EMP_NO


ANDOFICIO = @OFICIO


18/21

5

5!

55

5"

5#

5$

5%

"&

"1

"2

ANDDIR = @DIR





END

?

1 EXECBORRAEMPLEADO &$!#,'SUARE','EMPLEADO',0,'%"0#%01#',1"000,0,%0

3) Crear un procedimiento para insertar un empleado de la plantilla del :ospital. ;ara

poder insertar el empleado reali1aremos restriccionesurno.


19/21

l salario no superar" las #. ptas.

97pcional) ;odremos insertar por el c%digo del hospital o sala y por su nombre.

?

1

2

!

5

"

#

$

%

1&

11

12

1

1!

15

1"

1#

1$

1%

2&

21

22

2

2!

CREATEPROCEDUREINSERTAR_PLANTILLA

@HOSPITAL NVARCHAR(0)

,@SALA NVARCHAR(0)

,@EMPLEADO INT

,@APELLIDO NVARCHAR(0)

,@FUNCION NVARCHAR(0)

,@TURNO NVARCHAR(%)

,@SALARIO INT

AS

DECLARE@HOSPITAL% INT

DECLARE@SALA% INT

DECLARE@EMPLEADO% INT

DECLARE@APELLIDO% NVARCHAR(0)

DECLARE@FUNCION% NVARCHAR(0)

DECLARE@TURNO% NVARCHAR(%)

SELECT@EMPLEADO% = EMPLEADO_NO FROMPLANTILLA

WHEREEMPLEADO_NO = @EMPLEADO

IF (@EMPLEADO% ISNULL)

BEIN

SELECT@SALA% = SALA_COD FROMSALA

WHERENOMBRE = @SALA

SELECT@HOSPITAL% = HOSPITAL_COD FROMHOSPITAL

WHERENOMBRE = @HOSPITAL

IF (@SALA% ISNULLOR@HOSPITAL% ISNULL)

BEIN


20/21

25

2"

2#

2$

2%

&

1

2

!

5

"

#

$

%

!&

!1

!2

!

!!

!5

!"

!#

!$

!%

5&

51

52

PRINT 'LA SALA O EL HOSPITAL INTRODUCIDO NO EST7N EN LOS

DATOS3'

PRINT 'SALA3 '2 @SALA

PRINT 'HOSPITAL3 '2 @HOSPITAL

PRINT ''

PRINT 'NO SE HA REALIADO LA INSERCI4N'

END

ELSE

BEIN

SELECT@FUNCION% = FUNCION FROMPLANTILLA

WHEREFUNCION = @FUNCION

SELECT@TURNO% = T FROMPLANTILLA

WHERET = @TURNO

IF (@FUNCION% ISNULLOR@TURNO% ISNULL)

BEIN

PRINT 'LA FUNCI4N O EL TURNO INTRODUCIDOS NO SON

V7LIDOS'

PRINT 'FUNCION3 '2 @FUNCION

PRINT 'TURNO3 '2 @TURNO

PRINT ''


END

ELSE

BEIN

IF (@SALARIO 8 #00000)

BEIN

PRINT 'EL SALARIO M7XIMO PARA LOS TRABA.ADORES

DE LA PLANTILLA ES DE #00-000 PTAS'



21/21

5

5!

55

5"

5#

5$

5%

"&

"1

"2

"

"!

"5

""

"#

"$

"%

#&

#1

#2

END

ELSE

BEIN

INSERTINTOPLANTILLA(H9:;?_C9,S>?>_C9,E;?>9_N9,A;??

VALUES(@HOSPITAL%, @SALA%, @EMPLEADO

, @APELLIDO, @FUNCION, @TURNO, @SALARIO)

PRINT 'INSERCI4N REALIADA CORRECTAMENTE'

END

END

END

END

ELSE

BEIN

PRINT 'EL NGMERO DE EMPLEADO INTRODUCIDO EST7 REPETIDO3 '2

CAST(@EMPLEADO ASNVARCHAR(6))


END

?

1 EXECINSERTAR_PLANTILLA 'L> P>','R;>J?9 S-','EK9'

Problemas resueltos sobre Procedimientos almacenados

Documents

Transcript of Problemas resueltos sobre Procedimientos almacenados