August 21, 2008 at 4:53 pm
I have a table of "potential instant winning moments" each is a day and time. The first contestatnt that arrives after the moment has passed wins. They should only win ONE. So, if there are more than one "ripe winning moments" I only want to lock and update one of them..
So it first needs to check if a winning moment is ripe.
Then grab its identity (primary key) - hold in local variable
the use that primarykey to update that row as won
then retrun that identity to the application as a return parameter.
If no winning moment was ripe then I want to return that value as 0
I can make this work as multiple stored procs, but I would like to put them all in one if possible.
I know I am missing something, but I am not sure what..
Can anyone tell me how to fix this code?
USE [MYDATABASENAME]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_CheckAndUpdateWinner]
@Time smalldatetime,
@Date smalldatetime,
@ContestantKey int,
@randomWinningmomentKey int output
As
declare @@TheKey int
if exists
(select top 1 (RandomWinningMomentKey)
from RandomWinningMoments with(updlock)
where HasBeenWon = 0 and Drawingdate = @Date and WinningTime < @Time)
begin
set @@TheKey = IDENT_CURRENT('RandomWinningMoments')
update RandomWinningMoments
set HasBeenWon = '1', ContestantKey=@ContestantKey
where RandomWinningMomentKey = @@TheKey
set @RandomWinningMomentKey = @@TheKey
end
else
set @RandomWinningMomentKey = 0
August 21, 2008 at 5:10 pm
This is how I'd do it:
[font="Courier New"]CREATE PROCEDURE [dbo].[sp_CheckAndUpdateWinner]
@Time smalldatetime,
@Date smalldatetime,
@ContestantKey INT,
@randomWinningmomentKey INT output
AS
DECLARE @TheKey INT
SELECT TOP 1
@TheKey = RandomWinningMomentKey
FROM
RandomWinningMoments WITH(updlock)
WHERE
HasBeenWon = 0 AND
Drawingdate = @Date AND
WinningTime < @Time
IF @TheKey IS NOT NULL
BEGIN
UPDATE RandomWinningMoments
SET HasBeenWon = '1',
ContestantKey=@ContestantKey
WHERE
RandomWinningMomentKey = @TheKey
SET @RandomWinningMomentKey = @TheKey
END
ELSE
BEGIN
SET @RandomWinningMomentKey = 0
END
RETURN
[/font]
Your current code does not guarantee that @TheKey gets the same value as the row found in the IF EXISTS. From the BOL entry for IDENT_CURRENT:
Returns the last identity value generated for a specified table or view in any session and any scope.
Your upd lock means no one is inserting one when it runs, but you even said that there could be more than 1 that meets the criteria and with no ORDER BY there is no guarantee that you will get the most recent in your select.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2008 at 7:30 pm
Thanks! you rock!
That works perfectly!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply