Everything about database

Create Proc Proc_WatchingSetup
- - the position of rest that parameter is four kinds of parts
@leader Int,
@Male Int,
@Female Int,
@Driver IntAs

Declare @i Int - - tallyDeclare @j IntDeclare @PersonID IntDeclare @weekday IntDeclare @InsertPoint DatetimeDeclare @msg Char(20)

Set @i=1Set @j=1

- - general affairs begins
Begin Tran ReChange
- - delete the following tomorrow record (alignment already was changed delete previously)Delete From Watching Where [Datetime]%26gt;GetDate()

If (@@error %26lt;%26gt;0)
BeginRollback TranSet @msg='error1'ReturnEnd

- - the date of 30 days after reinserting and weekWhile @i%26lt;=30BeginInsert Watching (Datetime, weekDay) Values (dateadd(day, @i, {fn Curdate()}) , datepart(weekday, dateadd(day, @i, {fn Curdate()})) ) Set @i=@i+1End

If (@@error! =0)
BeginRollback TranSet @msg='error2'ReturnEnd

- - begin to use cursor

Set @j=1

- - / / / / numerate by order queueing up above all the leader's alignment

Declare Cur_watchingPerson Scroll Cursor ForSelect Personid From Watching_person Where Part=1 Order By OrderCode Asc

Open Cur_watchingPerson

- - move to begin the positionFetch Absolute @leader From Cur_watchingPerson Into @PersonIDIf @@fetch_status=-1Fetch First From Cur_watchingperson Into @PersonID

Set @i=1

While @i%26lt;=30Begin

While @j%26lt;=7- - the longest likelihood is 1 person inserts 7 daysBegin Update Watching Set LeaderId=@PersonID Where [datetime]=(dateadd(day, @i, {fn Curdate()}) ) If (@@error! =0)
BeginRollback TranSet @msg='error3'ReturnEnd
- - if be not worth,7 days exit circular substitution to on the weekendSelect @weekday=datepart(weekday, dateadd(day, @i, {fn Curdate()}) )Set @i=@i+1If (@weekday=1)Break
End

Set @j=1
Fetch Next From Cur_watchingperson Into @PersonID
- - if exceed border to turn round alignment the firstIf @@fetch_status=-1Fetch First From Cur_watchingperson Into @PersonIDEnd

Close Cur_watchingPersonDeallocate Cur_watchingPerson

- - / / / / / / / / / / / / driver very leader just the sameDeclare Cur_watchingPerson4 Scroll Cursor ForSelect Personid From Watching_person Where Part=4 Order By OrderCode Asc

Open Cur_watchingPerson4

- - move to begin the positionFetch Absolute @driver From Cur_watchingPerson4 Into @PersonIDIf @@fetch_status=-1Fetch First From Cur_watchingperson4 Into @PersonID

Set @i=1

While @i%26lt;=30BeginWhile @j%26lt;=7- - the longest likelihood is 1 person inserts 7 daysBegin Update Watching Set DriverId=@PersonID Where [datetime]=(dateadd(day, @i, {fn Curdate()}) ) If (@@error! =0)
Begin
- - Rollback TranSet @msg='error3'ReturnEnd

Select @weekday=datepart(weekday, dateadd(day, @i, {fn Curdate()}) )Set @i=@i+1If (@weekday=1)Break
End

Set @j=1Fetch Next From Cur_watchingperson4 Into @PersonID
- - if exceed border to turn round alignment the firstIf @@fetch_status=-1Fetch First From Cur_watchingperson4 Into @PersonIDEnd

Close Cur_watchingPerson4Deallocate Cur_watchingPerson4

- - / / / / / / / / / / /

- - fellow everyday evening shift of 1 person value is relatively easyDeclare Cur_watchingPerson2 Scroll Cursor ForSelect Personid From Watching_person Where Part=2 Order By OrderCode Asc

Open Cur_watchingPerson2

- - move to begin the positionFetch Absolute @male From Cur_watchingPerson2 Into @PersonIDIf @@fetch_status=-1Fetch First From Cur_watchingperson2 Into @PersonID

Set @i=1

While @i%26lt;=30Begin
Update Watching Set MaleId=@PersonID Where [datetime]=(dateadd(day, @i, {fn Curdate()}) ) If (@@error! =0)
BeginRollback TranSet @msg='error3'ReturnEnd
Set @i=@i+1

Fetch Next From Cur_watchingperson2 Into @PersonID
- - if exceed border to turn round alignment the firstIf @@fetch_status=-1 Fetch First From Cur_watchingperson2 Into @PersonID
End

Close Cur_watchingPerson2Deallocate Cur_watchingPerson2

- - the aunt is weekly 2 people are worth six weekday day shiftDeclare Cur_watchingPerson3 Scroll Cursor ForSelect Personid From Watching_person Where Part=3 Order By OrderCode Asc

Open Cur_watchingPerson3

Fetch Absolute @female From Cur_watchingPerson3 Into @PersonIDIf @@fetch_status=-1Fetch First From Cur_watchingperson3 Into @PersonID

Set @i=1

While @i%26lt;=30Begin

Select @weekday=[weekday] From Watching Where [datetime]=(dateadd(day, @i, {fn Curdate()}) )

- - judgement has the half talent on the weekend to be on duty only arrange 2 peopleIf @weekday=7 Or @weekday=1Begin

- - insert the firstUpdate Watching Set Female1=@PersonID Where [datetime]=(dateadd(day, @i, {fn Curdate()}) )
If (@@error! =0)
BeginRollback TranSet @msg='error3'ReturnEnd

Fetch Next From Cur_watchingperson3 Into @PersonID
- - if exceed border to turn round alignment the firstIf @@fetch_status=-1 Fetch First From Cur_watchingperson3 Into @PersonID
- - insert the secondUpdate Watching Set Female2=@PersonID Where [datetime]=(dateadd(day, @i, {fn Curdate()}) )
If (@@error! =0)
Begin
- - Rollback TranSet @msg='error3'ReturnEnd

End
Set @i=@i+1Fetch Next From Cur_watchingperson3 Into @PersonID
- - if exceed border to turn round alignment the firstIf @@fetch_status=-1 Fetch First From Cur_watchingperson3 Into @PersonID
End

Close Cur_watchingPerson3Deallocate Cur_watchingPerson3

Commit Tran