September 15, 2008 at 5:41 pm
Working on a stored proceedure that will select a random row in the table, update it as "won" and add winner info and then return
select * from table where (just the row I randomly selected and updated)
This is what I have so far.. Am I going in the right direction? It is not working... ;(
Thanks!
CREATE PROCEDURE [dbo].[sp_GetInstantWinCodePlusUpdate]
@WinDate smalldatetime,
@ContestantKey int,
@RandomWinningMomentKey int
As
declare @@TheKey int
begin tran
select @@TheKey = top 1 InstantWinKey
from InstantWin with(updlock)
where HasBeenWon = 0 order by newid()
update InstantWin
set HasBeenWon = '1', WinDate=@WinDate , ContestantKey=@ContestantKey , RandomWinningMomentKey=@RandomWinningMomentKey
where InstantWinKey = @@TheKey
commit
select * from InstantWin where InstantWinKey = @@TheKey
September 16, 2008 at 12:31 am
me (9/15/2008)
Working on a stored proceedure that will select a random row in the table, update it as "won" and add winner info and then returnselect * from table where (just the row I randomly selected and updated)
This is what I have so far.. Am I going in the right direction? It is not working... ;(
Thanks!
CREATE PROCEDURE [dbo].[sp_GetInstantWinCodePlusUpdate]
@WinDate smalldatetime,
@ContestantKey int,
@RandomWinningMomentKey int
As
declare @@TheKey int
begin tran
select @@TheKey = top 1 InstantWinKey
from InstantWin with(updlock)
where HasBeenWon = 0 order by newid()
update InstantWin
set HasBeenWon = '1', WinDate=@WinDate , ContestantKey=@ContestantKey , RandomWinningMomentKey=@RandomWinningMomentKey
where InstantWinKey = @@TheKey
commit
select * from InstantWin where InstantWinKey = @@TheKey
You said that the code is not working. What exactly is the issue that you have? Logically, there is no problem with the code.
September 16, 2008 at 12:36 am
"declare @@TheKey int". should'nt it be declare @TheKey int.
selecting top 1 may not be very random.
"Keep Trying"
September 16, 2008 at 8:33 am
There is something not quite right with the TOP syntax. Any idea?
Msg 156, Level 15, State 1, Procedure sp_GetInstantWinCodePlusUpdate, Line 16
Incorrect syntax near the keyword 'top'.
Msg 319, Level 15, State 1, Procedure sp_GetInstantWinCodePlusUpdate, Line 17
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
September 16, 2008 at 8:34 am
I thought it was @@ for local variables?
Isn't order by new id() random?
September 16, 2008 at 9:58 am
You've got the order wrong in the SELECT TOP... statement. Try it like this:
select top 1 @@TheKey = InstantWinKey
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 9:59 am
Oh yeah, ... and do change to @key instead of @@Key.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 10:44 am
rbarryyoung Rocks!
Thank you Lord Vader!
September 16, 2008 at 11:29 am
Thanks for the feedback, me.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply