Stored Procedure

Stored Procedure:

Stored Procedures are precompiled Sql server objects which store the Sql queries and executes whenever the stored procedure is called. In this tutorial we will see how to work with stored procedure. The following things are discussed in this article.

1. Create and Execute Stored Procedure.
2. Stored Procedure with parameter.
3. Stored Procedure with output parameter.
4. Return a value from stored procedure.
5. Call a Stored Procedure from another Stored Procedure

1. Create and Execute Stored Procedure.
To start with we will see a simple stored procedure which is used to retrieve records.
The following code is used to create the stored procedure.

create procedure retrieverecords            
as 
begin   
select * from employeedetails 
end

The following code is used to execute the stored procedure.

execute retrieverecords.

Stored Procedure can be used with Sql Command object as follows:

cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
//Initialize the connection object.
SqlConnection cnn = new SqlConnection(cnnstring);
SqlDataAdapter da = new SqlDataAdapter("retrieverecords", cnn);
DataSet ds = new DataSet();
da.Fill(ds, "employee");
GridView1.DataSource = ds;
GridView1.DataBind();

2. Stored Procedure With parameters:
You can create a stored procedure which inserts record(s) to the table by using the following query which takes employee details as input parameters.

create procedure insertrecords
@empid int,
@empname varchar(234),
@phoneno varchar(234),
@address varchar(234),
@dob varchar(234)
as
begin
insert into employeedetails values (@empid,@empname,@phoneno,@address,@dob)
end

You can execute the stored procedure in the following way:

 execute insertrecords 1,’Pinto’,’8456896542’,’Hyderabad’,’20/04/1989’.

Using the stored procedure with Sql Command Object:

The stored procedure can be used with the Sql Command object by using the following code.
        cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
        SqlConnection cnn = new SqlConnection(cnnstring);
        SqlCommand cmd = new SqlCommand("insertrecords", cnn);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@empid", txtempid.Text));
        cmd.Parameters.Add(new SqlParameter("@empname", txtempname.Text));
        cmd.Parameters.Add(new SqlParameter("@phoneno", txtphoneno.Text));
        cmd.Parameters.Add(new SqlParameter("@dob", txtdob.Text));
        cmd.Parameters.Add(new SqlParameter("@address", txtaddress.Text));
        cnn.Open();
        cmd.ExecuteNonQuery();
        cnn.Close();

3. Stored Procedure with output parameter:
The following stored procedure takes employeeid as input and returns employeename as output parameter.The following query is used to create a stored procedure with output parameter.

create procedure getemployeename
@empid int,
@empname varchar(234) output
as
begin
select @empname = employeename  from employeedetails where empolyeeid = 100
print @empname
end

You can execute the above stored procedure by using the below query:

Declare @empname varchar(234)                                                                                                                                             
execute getemployeename 1000, @empname

The following csharp code helps to use the stored procedure with your front end application:

  cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
        SqlConnection cnn = new SqlConnection(cnnstring);
        SqlCommand cmd = new SqlCommand("getemployeename", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int)).Value = txtempid.Text;
        cmd.Parameters.Add(new SqlParameter("@empname", SqlDbType.VarChar,236)).Direction = ParameterDirection.Output;
        cnn.Open();
        cmd.ExecuteNonQuery();
        Response.Write("EmployeeName:" + cmd.Parameters["@empname"].Value);
        cnn.Close();
        

4. Return a value from stored procedure:
The following query is used to create a stored procedure that returns value.

create procedure addrecords
@empid int output,
@empname varchar(234),
@phoneno varchar(234),
@address varchar(234),
@dob varchar(234)
as
begin
insert into employee(EmpName,PhoneNumber,Address,DOB)
values(@empname,@phoneno,@address,@dob)
select @empid = @@identity
return @empid
end

The stored procedure can be used in the application by using the following csharp code:

  // Read the value of connectionstring form the web.config file and assign it to cnnstring variable.
        cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
        SqlConnection cnn = new SqlConnection();
        //connection properties.
        cnn.ConnectionString = cnnstring;
        SqlCommand cmd = new SqlCommand("addrecords", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@empname", SqlDbType.VarChar, 235)).Value = txtempname.Text;
        cmd.Parameters.Add(new SqlParameter("@phoneno", SqlDbType.VarChar, 235)).Value = txtphoneno.Text;
        cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 235)).Value = txtaddress.Text;
        cmd.Parameters.Add(new SqlParameter("@dob", SqlDbType.VarChar, 235)).Value = txtdob.Text;
        cmd.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue;
        cnn.Open();
        int records = (int)cmd.ExecuteNonQuery();
        string id = cmd.Parameters["@empid"].Value.ToString();
        Response.Write("Record(" + records + ")with ID" + cmd.Parameters["@empid"].Value + "has been sucessfully added.");
        cnn.Close(); 

5.Call a Stored Procedure from another Stored Procedure. Before we proceed we need to create the following table.

create table EmployeeDepartment
(
  ID int foreign key references employee(ID),
  Department varchar(234)
)

Now we will create a stored procedure which calls the previously created stored procedure getemployeename. This stored procedure takes employeeid as input and returns empname and empdept from two different tables.(employeedetails and EmployeeDepartment).

create procedure DisplayEmployeeInfo
@empid int,
@empname varchar(235) output,
@Dept varchar(235) output
as
begin
execute @empname = getemployeename @empname,@empid
select @Dept = department from employeedepartment
print @empname+':'+@Dept
end