How to create and execute Stored Procedured in Sql Server

What   is   Stored Procedure? 

A stored procedure is a subroutine available to applications that access a relational system. It actually stored in the database data dictionary. It is  collection of  query (Compiled) which storing in Database………

  Advantage:

             1.   Better  performance

             2.   Less  traffic

             3.   Security

             4.   Integrity

             5.   Code Re usability

     Disadvantage:

          1. no of call is very more use ,the process is  slow or performance  less

     2.  Same Data base  working

********************************************************************

Open Sq l Server .................  and  select New Query.......................

creating database test 

create database test

after creating database ,create table  p_customer in database test

use  test

create table p_customer (id int primary key,fname nvarchar(50),lname nvarchar(50),dob date)

******************************************************************

1./* creating procedure for insertting data in p_customer table */

create proc Ins_Data @id int,@fname nvarchar(50),@lname nvarchar(50),@dob date

as

Insert into p_customer values(@id ,@fname,@lname,@dob )

/* execute procedure for  */

exec Ins_Data 2,'kush','Tiwari', '02/23/1987'

***********************************************************************

2. creating procedure for selecting data

create proc selectdata

 as

Select * from p_customer

/* execute procedure for selectdata */

exec selectdata

****************************************************************

3. creating procedure for delete  data

create proc Delelet_Data @id int

as

delete from p_customer where id=@id

 execute procedure for Delelet_Data 

exec Delelet_Data 4

************************************************************************

4. creating procedure for updating data*/

create proc Update_Data @id int,@fname nvarchar(50),@lname nvarchar(50),@dob date

as

update p_customer set fname=@fname,lname=@lname,dob=@dob where id=@id

/* execute procedure for Update_Data */

exec Update_Data 1,'Lav','Tiwari','08/11/1987'

**********************************************************************

5.creating procedure for selecting data by Id

 create proc selectbyId @id int

 as

 select fname,lname,dob from p_customer where id=@id

 execute procedure for selectbyId 

 exec selectbyId 1

How to use this procedure in Asp.Net…………with  help web.config

Frist we  create connectionstring in <connectionStrings> with connection name="kush"

Write code in web.config…………………………………………………………………………………………

<configuration>

  <connectionStrings>

    <add name="kush" connectionString="Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>

  </connectionStrings>

  <system.web>

    <compilation debug="true" targetFramework="4.0"/>

  </system.web>

</configuration>

After  than  ……………………………………………………….Default.cs

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;

using System.Data;

// this is  using for  configration

using System.Configuration;

public partial class _Default : System.Web.UI.Page

{

    SqlConnection con;

    SqlCommand cmd;

    SqlDataReader dr;

    protected void Page_Load(object sender, EventArgs e)

    {

        con = new SqlConnection(ConfigurationManager.ConnectionStrings["kush"].ConnectionString);

       // con = n.ew SqlConnection("Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True");

    }

    protected void BSave_Click(object sender, EventArgs e)

    {

        con.Open();

        cmd = new SqlCommand("Ins_Data", con);

        cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtid.Text));

        cmd.Parameters.AddWithValue("@fname",txtfname.Text);

        cmd.Parameters.AddWithValue("@lname",txtlname.Text);

        cmd.Parameters.AddWithValue("@dob", txtdob.Text);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.ExecuteNonQuery();

        con.Close();

        Message.Text = "Add successfully Record";

       

 

    }

    protected void BUpdate_Click(object sender, EventArgs e)

    {

        con.Open();

        cmd = new SqlCommand("Update_Data", con);

        cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtid.Text));

        cmd.Parameters.AddWithValue("@fname", txtfname.Text);

        cmd.Parameters.AddWithValue("@lname", txtlname.Text);

        cmd.Parameters.AddWithValue("@dob", txtdob.Text);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.ExecuteNonQuery();

        con.Close();

        Message.Text = "Update successfully Record";

    }

    protected void BSelect_Click(object sender, EventArgs e)

    {

        con.Open();

        cmd = new SqlCommand("selectdata", con);

        cmd.CommandType = CommandType.StoredProcedure;

        dr = cmd.ExecuteReader();

        GridView1.DataSource = dr;

        GridView1.DataBind();

        con.Close();

    }

    protected void BDelete_Click(object sender, EventArgs e)

    {

        con.Open();

        cmd = new SqlCommand("Delelet_Data", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtid.Text));

        cmd.ExecuteNonQuery();

        con.Close();

        Message.Text = "Delete successfully Record";

    }

    protected void BSelectById_Click(object sender, EventArgs e)

    {

        con.Open();

        cmd = new SqlCommand("selectbyId", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@id",Convert.ToInt32(txtid.Text));

        dr = cmd.ExecuteReader();

        if (dr.Read())

        {

            txtfname.Text = dr["fname"].ToString();

            txtlname.Text = dr["lname"].ToString();

            txtdob.Text = dr["dob"].ToString();

        }

       else

        {

            Message.Text = "Id is  not exist";

        }

    }

}

creating procedure for cheak no even or odd

create proc even_odd @no int

as

begin

declare @res int

set @res=@no%2

if @res=0

print'Number is even'

else

print'Number is odd'

end

executing procedure for even_odd 

exec even_odd 41

Result---

Number is odd

Read More