SQL Server Stored Procedure Creation

Use [Master]
Go

Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author :**
-- Create Date: 2011-06-22
-- Description: Add a winning record
-- Return success or failure
-- ===================================================== ======
Create procedure prmgame_addprizesrecord
@ Intuserid int, -- User ID
@ Vchiptvname varchar (50), -- IPTV name
@ Intprizestype smallint -- weekly or ultimate prize 0: weekly prize 1: ultimate prize
@ Intprizesno int, -- prize number
@ Vchprizesname varchar (50) = NULL output, -- prize name
@ Outprizesno Int = 0 output, -- prize number
@ Intreturncode Int = 1 output, -- process the returned value
@ Vchmsg varchar (128) = Null Output -- Process Information
As
Begin
Begin transaction
Declare @ nowtime datetime
Declare @ nowprizessum int
Declare @ nowrank int
Declare @ nowweek smallint -- the week of the day
Declare @ lestweek smallint
Select @ nowtime = getdate () from DBO. sysobjects -- get system time
-- Obtain the week number
If not exists (select * From weektime where weekstarttime <= @ nowtime and weekendtime >=@ nowtime)
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'the prize cannot be added and the week number cannot be obtained. '
Rollback transaction
Return 1
End
Select @ nowweek = weekno from weektime where weekstarttime <= @ nowtime and weekendtime> = @ nowtime
Set @ lestweek = @ nowweek-1
Print @ lestweek
If (@ intprizestype = 0)
Begin
-- Weekly Prize
If exists (select * From prizesrecode where userid = @ intuserid and weekno = @ lestweek and prizestype = @ intprizestype)
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'Prize addition failed. The user has already received the prize. '
Rollback transaction
Return 1
End
If exists (select * from prizes where prizesno = @ intprizesno and prizesremainder> 0)
Begin
-- Sufficient prize quantity
Select @ nowprizessum = prizesremainder, @ vchprizesname = prizesname, @ outprizesno = prizesno from prizes where prizesno = @ intprizesno
Set @ nowprizessum = @ nowprizessum-1
Update prizes set prizesremainder = @ nowprizessum where prizesno = @ outprizesno
If @ error <> 0
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'prize addition failed, and prize table update failed. '
Rollback transaction
Return 1
End
End
Else
Begin
-- Insufficient prize quantity
Select top 1 @ region = prizesremainder, @ region = prizesname, @ outprizesno = prizesno from prizes where prizesremainder> 0 and prizestype = @ intprizestype order by prizesremainder DESC
Set @ nowprizessum = @ nowprizessum-1
Update prizes set prizesremainder = @ nowprizessum where prizesno = @ outprizesno
If @ error <> 0
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'prize addition failed, and prize table update failed. '
Rollback transaction
Return 1
End
End
Select @ nowrank = weekrank from weekrank where userid = @ intuserid and weekno = @ lestweek
Insert into prizesrecode (userid, iptvname, prizesno, prizestype, recodetime, weekno, gamerank) values (@ intuserid, @ region, @ region, @ intprizestype, @ nowtime, @ lestweek, @ nowrank)
If @ error <> 0
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'failed to add the prize, but failed to update the prize record table. '
Rollback transaction
Return 1
End
Set @ intreturncode = 0
Set @ vchmsg = 'prize obtained successfully'
Commit transaction
Return 0
End
Else if (@ intprizestype = 1)
Begin
-- Ultimate prize
If exists (select * From prizesrecode where userid = @ intuserid and prizestype = @ intprizestype)
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'Prize addition failed. The user has already received the prize. '
Rollback transaction
Return 1
End

If exists (select * from prizes where prizesno = @ intprizesno and prizesremainder> 0)
Begin
-- Sufficient prize quantity
Select @ nowprizessum = prizesremainder, @ vchprizesname = prizesname, @ outprizesno = prizesno from prizes where prizesno = @ intprizesno
Set @ nowprizessum = @ nowprizessum-1
Update prizes set prizesremainder = @ nowprizessum where prizesno = @ outprizesno
If @ error <> 0
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'prize addition failed, and prize table update failed. '
Rollback transaction
Return 1
End
End
Else
Begin
-- Insufficient prize quantity
Select top 1 @ region = prizesremainder, @ region = prizesname, @ outprizesno = prizesno from prizes where prizesremainder> 0 and prizestype = @ intprizestype order by prizesremainder DESC
Set @ nowprizessum = @ nowprizessum-1
Update prizes set prizesremainder = @ nowprizessum where prizesno = @ outprizesno
If @ error <> 0
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'prize addition failed, and prize table update failed. '
Rollback transaction
Return 1
End
End
Select @ nowrank = allrank from allrank where userid = @ intuserid
Insert into prizesrecode (userid, iptvname, prizesno, prizestype, recodetime, gamerank) values (@ intuserid, @ override, @ outprizesno, @ intprizestype, @ nowtime, @ nowrank)
If @ error <> 0
Begin
Set @ intreturncode = 1
Set @ vchmsg = 'failed to add the prize, but failed to update the prize record table. '
Rollback transaction
Return 1
End
Set @ intreturncode = 0
Set @ vchmsg = 'prize obtained successfully'
Commit transaction
Return 0

End
Set @ intreturncode = 1
Set @ vchmsg = 'prize addition failed. parameter input error. '
Rollback transaction
Return 1
End