create procedure

----1st Step---------------Create a Table----------------
CREATE TABLE UserRegistration (
C_Id int IDENTITY(1, 1) NOT NULL,
C_Name varchar(100) NULL,
C_Age varchar(100) NULL,
C_Country varchar(100) NULL
);

----2nd Step---------------Create a Stored Procedure----------------
CREATE  procedure [dbo].[SpMyProcedure] (
@C_Id int = null,
@C_Name varchar(100)= null,
@C_Age varchar(100)= null,
@C_Country varchar(100)= null,
@Is_Active bit = 1,
@Action varchar(100)= null
--@flag bit output-- return 0 for fail,1 for success
)
 
As
begin

if @Action='Select'

  select * from UserRegistration
 

if @Action = 'Insert'
Insert into UserRegistration values  (@C_Name, @C_Age, @C_Country,@Is_Active)

if @Action = 'Update'
Update  UserRegistration  set  C_Name = @C_Name,  C_Age = @C_Age,  C_Country = @C_Country where C_Id = @C_Id


if @Action = 'Delete'
--Delete from  UserRegistration  where  C_Id = @C_Id
UPDATE UserRegistration SET Is_Active=0 where C_Id=@C_Id

if @Action='Search'
 select * from UserRegistration where C_Name like  '_n%' or C_Name like  '_T%'
 --SELECT * FROM UserRegistration WHERE column LIKE IN ('Text%', 'Link%', 'Hello%', '%World%')

if @Action='Count'
   select count(C_id) as Total_Users from UserRegistration


end
----3rd Step---------------Now we can call stored procedure from our code like the following for Insert operation.----------------

protected void btnSave_Click(object sender, EventArgs e)  
{
    string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";
    SqlConnection cn = new SqlConnection(str);
    SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Action", "Insert");
    cmd.Parameters.AddWithValue("@Name", txtName.Text);
    cmd.Parameters.AddWithValue("@Age", txtAge.Text);
    cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}

----4th Step---------------We can call stored procedure from our code like the following for Update operation.----------------
protected void btnUpdate_Click(object sender, EventArgs e)  
{
    string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";
    SqlConnection cn = new SqlConnection(str);
    SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Action", "Update");
    cmd.Parameters.AddWithValue("@Name", txtName.Text);
    cmd.Parameters.AddWithValue("@Age", txtAge.Text);
    cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
    cmd.Parameters.AddWithValue("@Id", txtId.Text);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}

----5th Step---------------We can call stored procedure from our code like the following for Delete operation.----------------
protected void btnDelete_Click(object sender, EventArgs e)  
{
    string str = "server=Your Server Name; Initial Catalog=Your Database Name; User ID=User Id; Password=Your Password";
    SqlConnection cn = new SqlConnection(str);
    SqlCommand cmd = new SqlCommand("SpMyProcedure", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Action", "Delete");
    cmd.Parameters.AddWithValue("@Id", txtId.Text);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}