StoredProcedure

//SP  with out parameter
//to create SP
CREATE PROCEDURE SP_TestNoParam

AS
BEGIN   
    SELECT * FROM TBL_Details
END   
   
//to drop SP  
 drop procedure SP_TestNoParam
 //to view SP
 sp_helptext SP_TestNoParam
 //to execute SP
 SP_TestNoParam

-------------------
//SP with parameters

CREATE PROCEDURE SP_Test(
                        @P_ID INT                       
                        )
    AS
    BEGIN
        SELECT * FROM TBL_Details WHERE ID=@P_ID
    END 

 SP_Test 2


-----------------------
//insert SP
CREATE PROCEDURE SP_TestInsert(                           
                            @P_Name VARCHAR(100),
                            @P_Age INT
                              )
AS
BEGIN
    DECLARE @P_ID INT
    SET @P_ID=(SELECT ISNULL(MAX(ID),0) FROM TBL_Details) + 1
    INSERT INTO TBL_Details VALUES(@P_ID,@P_Name,@P_Age)
END

//caling SP
SP_TestInsert 'Test123', 33
-----------------------------------
    public void DataConnectionSP(string query,string name,int age)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True;Pooling=False";
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = query;
            com.Parameters.Add(new SqlParameter("@P_Name",name));
            com.Parameters.Add(new SqlParameter("@P_Age",age));
            com.CommandType = CommandType.StoredProcedure;

            com.ExecuteNonQuery();
        }

 -------------------------------

//In grid rowCommand put

string query = "SP_TestInsert";
oGridDL.DataConnectionSP(query,txtName.Text.ToString(),Convert.ToInt32(txtAge.Text));