I have a SQL query. I need convert it to a stored procedure.
I have created a query of student result along with ranking. This contains a condition as well. My requirement is to convert this query into a stored procedure. The SQL table name student01 containing Roll, Name, Maths, Science, English, Total, Sum, Average, OrderRank as columns.
Please help me out!
What I have tried:
Query:
create table student01
(
Rollno Int null,
Name Varchar (25),
Maths Int,
English Int,
Science Int,
Total Int null,
Average int,
ORDERRANK int
);
insert into student01
values(1,'api',90,80,90,0,0,0)
update student01
set Total = Maths + English + Science;
update student01
set Average = Total/3;
SELECT
Rollno, Name, Maths, English, Science, Total, Average, ORDERRANK,
CASE
WHEN Maths > 35 AND English > 35 AND Science > 35
THEN RANK() OVER (ORDER BY Average DESC)
ELSE 0
END AS StudentRank
INTO
#TEMP
FROM
student01
UPDATE student01
SET ORDERRANK = (SELECT TOP 1 STUDENTRANK FROM #TEMP
WHERE Rollno = student01.Rollno)
DROP TABLE #TEMP
SELECT *
FROM student01
ORDER BY ORDERRANK ASC