Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Combining Insert/Update to one Procedure

0.00/5 (No votes)
3 Jan 2012CPOL 7.5K  
CREATE PROCEDURE sp_Insert ( @ID int, @Name char(20), @Mode char(20) ) ASif @Mode='Insert in Employee'Begin insert into Employee(Employee_ID,Employee_Name)values(@ID,@Name)Endelse if @Mode='Insert in Student'Begin insert into...
SQL
CREATE PROCEDURE sp_Insert
	
	(
	@ID int,
	@Name char(20),
	@Mode char(20)
	)
	
AS
if @Mode='Insert in Employee'
Begin
	insert into Employee(Employee_ID,Employee_Name)values(@ID,@Name)
End
else if @Mode='Insert in Student'
Begin
    insert into Student(Student_ID,Student_Name)values(@ID,@Name)
End

	RETURN

============================================================================and on the buttons_click this will be coding...!

C#
private void Employee_Save_Click(object sender, EventArgs e)
       {
           SqlConnection conn = new SqlConnection(" ");
           conn.Open();
           SqlCommand cmd=new SqlCommand("SP_INSERT",conn);
           cmd.CommandType=CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("@ID",txtEmployeeID);
           cmd.Parameters.AddWithValue("@Name",txtEmployeeName);
           cmd.Parameters.AddWithValue("@Mode","Insert in Employee");
           cmd.ExecuteNonQuery();
           conn.Close();

       }
       private void Student_Save_Click(object sender, EventArgs e)
       {
           SqlConnection conn = new SqlConnection("")
           conn.Open();
           SqlCommand cmd = new SqlCommand("SP_INSERT", conn);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("@ID", txtStudentID);
           cmd.Parameters.AddWithValue("@Name",txtStudentName);
           cmd.Parameters.AddWithValue("@Mode", "Insert in Student");
           cmd.ExecuteNonQuery();
           conn.Close();
       }

May be there would be difference coding Format...!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)