I need to create a stored procedure that will be used to show the top student re

Question

I need to create a stored procedure that will be used to show the top student records based on gpa. An input parameter should be used to indicate the number of records to return...

I need to be able to execute exec spTopGPA(3) should return the top 3 student records based on gpa. I don't have to use ties. but ,I have to use dynamic sql and use the follow table schema.

CREATE TABLE [dbo].[student]( [StudentID] [int] IDENTITY(1,1) NOT NULL, [Firstname] [varchar](50) NOT NULL, [Lastname] [varchar](50) NOT NULL, [SS#] [int] NOT NULL, [GPA] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [StudentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], UNIQUE NONCLUSTERED ( [SS#] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[student] WITH CHECK ADD CHECK (([GPA]>=(0))) GO

Explanation / Answer

Stored Proc:

CREATE PROCEDURE ProductType (IN noOfRec INT)
BEGIN

  
SELECT *
FROM student
WHERE
(
gpa IN
(
SELECT TOP (noOfRec) gpa
FROM student as table1
GROUP BY gpa
ORDER BY gpa DESC
)
);

END

Comple this codebase and pass the parameter you will get the output.

Note: Drop a comment for any concern.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.