30. maj 2007 - 15:48Der er
6 kommentarer og 1 løsning
SP med kun et resultset samt indsættelse hvis alt lykkedes
Jeg er temmlig frustreret over at jeg ikke kan få det til at virke på den måde jeg gerne vil. Jeg vil med denne stored procedure kun have et resultset, samt at der kun skal opdateres når det hele exec som ønsket. ellers skal den ikke gøre noget. På forhånd tak..
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[requestDonation] -- Parameters @hospitalID int, @bloodTypeID int, @amount int
AS -- Local variables DECLARE @now dateTime DECLARE @later dateTime
-- Init variables SET @now = GETDATE() SET @later = DATEADD(d , 2, GETDATE())
-- Make new delivery get id INSERT INTO delivery(orderDate, deliveryDate, hospitalID) VALUES (@now, @later, @hospitalID) DECLARE @deliveryID int SET @deliveryID = (SELECT MAX(deliveryID) FROM delivery)
DECLARE @count int SET @count = 0
WHILE @count < @amount BEGIN
DECLARE @donationID int
SET @donationID = ( SELECT TOP 1 donationID FROM donation, donor, bloodType, delivery WHERE donation.cpr = donor.cpr AND donor.bloodTypeID = @bloodTypeID AND bloodType.bloodTypeID = @bloodTypeID AND DATEDIFF(day, donation.date, GETDATE()) < 10 AND request = 'false' GROUP BY donationID)
UPDATE donation SET request = 1 WHERE donationID = @donationID INSERT INTO deliveryList (donationID, deliveryID) VALUES (@donationID, @deliveryID)
-- Send back resultset with the SELECT donationID, bloodType, bloodPercentage, date, request FROM donation, donor, bloodType WHERE donationID = @donationID AND donor.bloodTypeID = @bloodTypeID AND bloodType.bloodTypeID = @bloodTypeID GROUP BY donationID, bloodType, bloodPercentage, date, request
Har fundet ud af det, men så mangler jeg bare at tjekke om deliveryID ikke er null, og om update og insert bliver exec..
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[requestDonation] -- Parameters @hospitalID int, @bloodTypeID int, @amount int
AS -- Local variables DECLARE @now dateTime DECLARE @later dateTime
-- Init variables SET @now = GETDATE() SET @later = DATEADD(d , 2, GETDATE())
-- Make new delivery get id INSERT INTO delivery(orderDate, deliveryDate, hospitalID) VALUES (@now, @later, @hospitalID) DECLARE @deliveryID int SET @deliveryID = (SELECT MAX(deliveryID) FROM delivery)
DECLARE @count int SET @count = 0
WHILE @count < @amount BEGIN
DECLARE @donationID int
SET @donationID = ( SELECT TOP 1 donationID FROM donation, donor, bloodType, delivery WHERE donation.cpr = donor.cpr AND donor.bloodTypeID = @bloodTypeID AND bloodType.bloodTypeID = @bloodTypeID AND DATEDIFF(day, donation.date, GETDATE()) < 10 AND request = 'false' GROUP BY donationID)
UPDATE donation SET request = 1 WHERE donationID = @donationID INSERT INTO deliveryList (donationID, deliveryID) VALUES (@donationID, @deliveryID) SET @count = @count + 1 END -- Send back resultset with the SELECT donation.donationID, bloodType, bloodPercentage, date, request FROM donation, donor, bloodType, deliveryList WHERE deliveryList.deliveryID = @deliveryID AND deliveryList.donationID = donation.donationID AND donation.cpr = donor.cpr AND donor.bloodTypeID = bloodType.bloodTypeID GROUP BY donation.donationID, bloodType, bloodPercentage, date, request
-- Local variables DECLARE @now dateTime DECLARE @later dateTime DECLARE @rowsAffectedByUpdate int ,@rowsAffectedByInsert int
-- Init variables SET @now = GETDATE() SET @later = DATEADD(d , 2, GETDATE())
-- Make new delivery get id INSERT INTO delivery(orderDate, deliveryDate, hospitalID) VALUES (@now, @later, @hospitalID) DECLARE @deliveryID int SET @deliveryID = (SELECT MAX(deliveryID) FROM delivery)
DECLARE @count int SET @count = 0
WHILE @count < @amount BEGIN
DECLARE @donationID int
SET @donationID = ( SELECT TOP 1 donationID FROM donation, donor, bloodType, delivery WHERE donation.cpr = donor.cpr AND donor.bloodTypeID = @bloodTypeID AND bloodType.bloodTypeID = @bloodTypeID AND DATEDIFF(day, donation.date, GETDATE()) < 10 AND request = 'false' GROUP BY donationID)
UPDATE donation SET request = 1 WHERE donationID = @donationID SET @rowsAffectedByUpdate = @rowsAffectedByUpdate+ @@ROWCOUNT INSERT INTO deliveryList (donationID, deliveryID) VALUES (@donationID, @deliveryID) SET @rowsAffectedByInsert = @rowsAffectedByInsert + @@ROWCOUNT SET @count = @count + 1 END -- Send back resultset with the SELECT donation.donationID, bloodType, bloodPercentage, date, request FROM donation, donor, bloodType, deliveryList WHERE deliveryList.deliveryID = @deliveryID AND deliveryList.donationID = donation.donationID AND donation.cpr = donor.cpr AND donor.bloodTypeID = bloodType.bloodTypeID GROUP BY donation.donationID, bloodType, bloodPercentage, date, request
SELECT donation.donationID, bloodType, bloodPercentage, date, request,@rowsAffectedByUpdate as rowsAffectedByUpdate,@rowsAffectedByInsert as rowsAffectedByInsert FROM donation, donor, bloodType, deliveryList WHERE deliveryList.deliveryID = @deliveryID AND deliveryList.donationID = donation.donationID AND donation.cpr = donor.cpr AND donor.bloodTypeID = bloodType.bloodTypeID GROUP BY donation.donationID, bloodType, bloodPercentage, date, request
Du kan lave dit delivery id med: DECLARE @deliveryID int -- Make new delivery get id INSERT INTO delivery(orderDate, deliveryDate, hospitalID) VALUES (@now, @later, @hospitalID) SELECT @deliveryID = SCOPE_IDENTITY
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.