SELECT MAX (score), MIN (score) INTO: ll_maxscore, : ll_minscore FROM exams WHERE SBT="001";
K=ll_maxscore ll_minscore + 1
I=1
FOR j=1 TO k
The UPDATE exams SET CRD=: WHERE I score=: ll_maxscore + 1 - : j AND SBT='001';
IF the SQLCA. SQLNRows & gt; 0 THEN
I=I + SQLCA. SQLNRows
END the IF
NEXT

How to implement this functionality in SQL stored procedure?

xie

CodePudding user response:

Create procedure sp_pro1 @ SBT varchar (20)
As
Declare @ ll_maxscore int, @ ll_minscore int, int, @ k @ j int, @ I int
SELECT @ ll_maxscore=MAX (score), @ ll_minscore=MIN (score) FROM exams WHERE SBT=@ SBT
If @ @ rowcount=0 return - 1
Select @ + 1, k=@ ll_maxscore - @ ll_minscore @ I=1, @ j=1
While @ j & lt;=@ k
The begin
The UPDATE exams SET CRD=@ the WHERE I score=@ ll_maxscore + 1 - @ j AND SBT=@ SBT
Select @ I=@ I + @ @ rowcount, @ j=@ j + 1
If @ @ error & lt;> Return 0-1
End
Return 0

CodePudding user response:

If you don't involve processing interface, can be changed

CodePudding user response:

First of all thank you upstairs to answer
There is a condition not use to
IF the SQLCA. SQLNRows & gt; 0 THEN
I=I + SQLCA. SQLNRows
END the IF

This process in the storage, how to replace?

The UPDATE exams SET CRD=@ the WHERE I score=@ ll_maxscore + 1 - @ j AND SBT=@ SBT
Select @ I=@ I + @ @ rowcount, @ j=@ j + 1
So did not come out according to the order, I want to realize ranking function

CodePudding user response:

Create procedure sp_pro1 @ SBT varchar (20)
As
The create table # sbt001 (score a decimal (18, 2) null default 0,
Id a decimal (18, 0) identity (1, 1) not null)
Insert # sbt001 (score)
Select distinct score from exams where SBT=@ SBT order by score desc
The update exams set CRD=# sbt001. Id from exams, # sbt001 where exams. The score=# sbt001. Score
Return 0


Scattered points -

CodePudding user response:

Pay attention to pick up points