December 16, 2008 at 11:37 am
Hi everyone
i'm woking with sql srvr 2005 express and the following sp is not working, here it goes:
ALTER PROCEDURE dbo.spEliminarEjemplarSolicit
@IDEjemplar int,
@IDTitulo int
AS
DECLARE @NumEjempSol int
DECLARE @IDSolicitud int
DELETE FROM EjemplaresSolicitados
WHERE (IDEjemplar = @IDEjemplar) AND (IDTitulo = @IDTitulo)
SET @NumEjempSol = (SELECT EjemplaresSolicitados
FROMEjemplaresSolicitados
WHERE (IDEjemplar = @IDEjemplar) AND (IDTitulo = @IDTitulo))
SET @NumEjempSol = @NumEjempSol - 1
SET @IDSolicitud = (SELECT SolicitudesTitulos.IDSolicitud
FROM EjemplaresSolicitados INNER JOIN
Titulos ON EjemplaresSolicitados.IDTitulo = Titulos.IDTitulo INNER JOIN
SolicitudesTitulos ON Titulos.IDTitulo = SolicitudesTitulos.IDTitulo
WHERE (EjemplaresSolicitados.IDEjemplar = @IDEjemplar) AND
(EjemplaresSolicitados.IDTitulo = @IDTitulo))
IF (@NumEjempSol <= 0)
BEGIN
DELETE FROM SolicitudesTitulos
WHERE (IDSolicitud = @IDSolicitud) AND (IDTitulo = @IDTitulo)
DELETE FROM FinalidadesTitulos
WHERE (IDTitulo = @IDTitulo)
DELETE FROM AutoresTitulos
WHERE (IDTitulo = @IDTitulo)
DELETE FROM Titulos
WHERE (IDTitulo = @IDTitulo)
END
ELSE
BEGIN
UPDATE EjemplaresSolicitados
SET EjemplaresSolicitados = @NumEjempSol
WHERE (IDEjemplar = @IDEjemplar) AND (IDTitulo = @IDTitulo)
END
this code execute only the bold underlined "delete" sentence, and the bold italic "if" sentence it's always false, but never execute the "update" under the "else" sentence, i know that because i'v used print to see the value of some variables. I tried to use function to replace the "select" used to set the paremeters values, but didn't work.
Can anyone tell me why this happens? or what's wrong whit my code.
Thanks
December 16, 2008 at 11:53 am
You may want to see the comments I made in the code, but bottom line, your code is your problem.
ALTER PROCEDURE dbo.spEliminarEjemplarSolicit
@IDEjemplar int,
@IDTitulo int
AS
BEGIN
DECLARE @NumEjempSol int
DECLARE @IDSolicitud int
DELETE FROM
EjemplaresSolicitados
WHERE
(IDEjemplar = @IDEjemplar)
AND (IDTitulo = @IDTitulo) -- delete all records for these values
SET @NumEjempSol = (SELECT
EjemplaresSolicitados
FROM
EjemplaresSolicitados
WHERE
(IDEjemplar = @IDEjemplar)
AND (IDTitulo = @IDTitulo)) -- select all records for the values deleted above, is null
SET @NumEjempSol = @NumEjempSol - 1 -- subtract 1 from null, is null
SET @IDSolicitud = (SELECT
SolicitudesTitulos.IDSolicitud
FROM
EjemplaresSolicitados
INNER JOIN Titulos
ON EjemplaresSolicitados.IDTitulo = Titulos.IDTitulo
INNER JOIN SolicitudesTitulos
ON Titulos.IDTitulo = SolicitudesTitulos.IDTitulo
WHERE
(EjemplaresSolicitados.IDEjemplar = @IDEjemplar) AND
(EjemplaresSolicitados.IDTitulo = @IDTitulo)) -- again select all records for the values that were deleted, null
IF (@NumEjempSol <= 0)
BEGIN
DELETE FROM
SolicitudesTitulos
WHERE
(IDSolicitud = @IDSolicitud)
AND (IDTitulo = @IDTitulo)
DELETE FROM
FinalidadesTitulos
WHERE
(IDTitulo = @IDTitulo)
DELETE FROM
AutoresTitulos
WHERE
(IDTitulo = @IDTitulo)
DELETE FROM
Titulos
WHERE
(IDTitulo = @IDTitulo)
END
ELSE
BEGIN
UPDATE EjemplaresSolicitados SET
EjemplaresSolicitados = @NumEjempSol
WHERE
(IDEjemplar = @IDEjemplar)
AND (IDTitulo = @IDTitulo) -- attempt to update records that were delete at the top
END
END
December 16, 2008 at 12:06 pm
Hi
Thanks for your reply, what the hell i was thinking, you're absolutely right, maybe that i was starving and i was only thinking in food.
Thanks a lot for your help
December 16, 2008 at 12:23 pm
Here is the updated code maybe can help anyone (i don't think so, because of it's simplicity), anyway here goes:
ALTER PROCEDURE dbo.spEliminarEjemplarSolicit
@IDEjemplar int,
@IDTitulo int
AS
DECLARE @NumEjempSol int, @IDSolicitud int
SET @NumEjempSol = (SELECT EjemplaresSolicitados
FROMEjemplaresSolicitados
WHERE (IDEjemplar = @IDEjemplar) AND (IDTitulo = @IDTitulo))
SET @IDSolicitud = (SELECT SolicitudesTitulos.IDSolicitud
FROM EjemplaresSolicitados INNER JOIN
Titulos ON EjemplaresSolicitados.IDTitulo = Titulos.IDTitulo INNER JOIN
SolicitudesTitulos ON Titulos.IDTitulo = SolicitudesTitulos.IDTitulo
WHERE (EjemplaresSolicitados.IDEjemplar = @IDEjemplar) AND
(EjemplaresSolicitados.IDTitulo = @IDTitulo))
DELETE FROM EjemplaresSolicitados
WHERE (IDEjemplar = @IDEjemplar) AND (IDTitulo = @IDTitulo)
SET @NumEjempSol = @NumEjempSol - 1
IF (@NumEjempSol <= 0)
BEGIN
DELETE FROM SolicitudesTitulos
WHERE (IDSolicitud = @IDSolicitud) AND (IDTitulo = @IDTitulo)
DELETE FROM FinalidadesTitulos
WHERE (IDTitulo = @IDTitulo)
DELETE FROM AutoresTitulos
WHERE (IDTitulo = @IDTitulo)
DELETE FROM Titulos
WHERE (IDTitulo = @IDTitulo)
END
ELSE
BEGIN
UPDATE EjemplaresSolicitados
SET EjemplaresSolicitados = @NumEjempSol
WHERE (IDTitulo = @IDTitulo)
END
December 16, 2008 at 12:33 pm
Well, now that you have updated the code, what is wrong? Also, for better answers to your questions, please read and follow the guidlines provided in the article I have linked below in my signature block regarding asking for assistance. To really help you further, we really need more information.
December 16, 2008 at 12:41 pm
please forgive me, my english is not very good, the code that i've sent before doesn't have problems, it's only the updated an working code.
Regards
December 16, 2008 at 12:44 pm
What is working and what is not working, or maybe you can explain with some data what you want to happen. It's a little confusing as to what you think isn't working. Lynn pointed out one problem, you are deleting rows and then trying to select those same rows.
December 16, 2008 at 12:46 pm
Just to be sure, your last posted code was your corrected code and is working correctly, right??
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply