Find The Nth Highest Salary Using Stored Procedure in ASP.NET

Sanjoli Gupta
12y
17.3k
0
0

Article

Introduction

In this article we will find nth highest salary of the employee with the help of Stored Procedure using ASP.NET. This is a very helpful article for the ASP.NET developer, to find the 1^st,2^nd, 3^rd......nth Highest salary of the employee.

What are Stored Procedure

A stored procedure is an already written SQL statement that is saved in the database. If we are using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database's command environment (I am a SQL Server kind of guy, and run stored procedures from the Query Analyzer), using the exec command. In short, Precompiled SQL statement is known as Stored Procedure. An example is:

exec usp_displayallusers

The name of the stored procedure is "usp_displayallusers", and "exec" tells SQL Server to execute the code in the stored procedure. (Note: "usp_" in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as:

SELECT * FROM USERLIST

Benefits of stored Procedure

Precompiled
Network load reduces
Security Mechanism

Creating a Stored Procedure

You create stored procedures in the SQL Server Management Studio using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.

CREATE PROCEDURE StoredProcedureName AS
...

The following code creates a stored procedure by the name "MyStoredProcedure"

Modifying a Stored Procedure

If you need to modify an existing stored procedure, you simply replace the CREATE with ALTER.

ALTER PROCEDURE MyStoredProcedure AS
...

Running a Stored Procedure

You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above stored procedure, type the following:

EXEC MyStoredProcedure

If the stored procedure has spaces in its name, enclose it between double quotes:

EXEC "My Stored Procedure"

Implementation for nth highest salary

Create a page named nth highest salary. Drag and drop a dropdownlist from the toolbox. Below I am giving complete code for .aspx page and .aspx.cs page.

Code for .aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Select a Value"></asp:Label>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" Height="59px"
            OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" Width="96px">
            <asp:ListItem>- - -</asp:ListItem>
            <asp:ListItem>1</asp:ListItem>
            <asp:ListItem>2</asp:ListItem>
            <asp:ListItem>3</asp:ListItem>
            <asp:ListItem>4</asp:ListItem>
            <asp:ListItem>5</asp:ListItem>
        </asp:DropDownList>
        <br />
        <br />
        <br />
        <asp:Button ID="Button2" runat="server" Text="Salary is " Height="20px" OnClick="Button2_Click"
            Width="112px" />
        <p style="height: 30px">
        </p>
    </div>
    </form>
</body>
</html>

Code for aspx.cs page

using System;

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        SqlConnection conn;
        SqlCommand comm;
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            conn = new SqlConnection("server=.;database=sanjoli;user=sa;password=wintellect");
            conn.Open();
            comm = new SqlCommand("SELECT TOP 1 emp_salary FROM (SELECT DISTINCT TOP" + DropDownList1.SelectedItem + " emp_salary FROM
emp1 ORDER BY emp_salary DESC) a ORDER BY emp_salary", conn);
            Button2.Text = comm.ExecuteScalar().ToString();
            conn.Close();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
        }
    }
}

See output in this image

When we choose 1 from the Dropdownlist,

It will give you the highest salary from the given table in database

ist highest.gif

Similarly when we select 2 from dropdown list,it will give the 2nd highest salary

2nd high.gif

SqlServer for Database

--Create Procedure
CREATE PROCEDURE usp_ins21
@emp_id int,
@emp_name varchar(20),
@emp_salary int
as
insert into emp1 values (@emp_id ,@emp_name ,@emp_salary );
exec usp_ins21 1,'sanjoli',12000;
exec usp_ins21 2,'ram',2000;
exec usp_ins21 3,'shyam',1000;
exec usp_ins21 4,'sarthak',9000;
exec usp_ins21 5,'aman',11000;
exec usp_ins21 6,'neha',4000;
select *from emp1
--nth highest salary
CREATE PROCEDURE usp_max1
as
SELECT TOP 1 emp_salary
FROM (
SELECT DISTINCT TOP 4 emp_salary
FROM emp1
ORDER BY emp_salary DESC) a
ORDER BY emp_salary