Stored Procedure For reference

Stored Procedure For reference


CREATE PROCEDURE sp_Insertion1
(@sempId int,@sempname varchar(50),@sgender varchar(50),@sstreet varchar(50),@semail varchar(50),@spincode varchar(50))
as
insert into employeear(fempid,fempname,fgender,fstreet,femail,fpincode)
values
(@sempId,@sempname,@sgender,@sstreet,@semail,@spincode)


private void btnNew_Click(object sender, EventArgs e)
{

string empid,empname, gender, street, email, pincode;
empid = txtEmpId.Text;
empname = txtEmpName.Text;
gender = comboGender.SelectedItem.ToString();
street = rtStreet.Text;
email = txtEmail.Text;
pincode = txtPincode.Text;
int empidint = int.Parse(empid);
SqlCommand dbcmd = new SqlCommand();
SqlConnection conn = new SqlConnection("Server=192.168.1.5;" + "Database=trainee;"+ "User ID=senthilkumar;" + "Password=gtpl;" + "Trusted_Connection=false;");
SqlCommand command = new SqlCommand("sp_Insertion1", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@sempid", SqlDbType.Int).Value = empidint;
command.Parameters.Add("@sempname",SqlDbType.VarChar).Value = empname;
command.Parameters.Add("@sgender", SqlDbType.VarChar).Value = gender;
command.Parameters.Add("@sstreet", SqlDbType.VarChar).Value = street;
command.Parameters.Add("@semail", SqlDbType.VarChar).Value = email;
command.Parameters.Add("@spincode",SqlDbType.VarChar).Value = pincode;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();
}
Update Stored Procedure
ALTER PROCEDURE sp_updation(@sempId int
,@sempname varchar(50)
,@sgender varchar(50)
,@sstreet varchar(50)
,@semail varchar(50)
,@spincode varchar(50))
as
update employeear

set fempid=@sempId
,fempname=@sempname
,fgender=@sgender
,fstreet=@sstreet
,femail=@semail
,fpincode=@spincode
where fempid=@sempId



Delete data from stored procedure


create procedure sp_address_delete(@sid int)as
delete from address where tid=@sid



Select data from Stored Procedure



create procedure sp_address_show(@sid int)
as
select * from address where tid=@sid



Select stored procedure with data type conversion

ALTER procedure [dbo].[sp_address_show](@sid int)
as
select
ttitle,
tname,
taddress,
tlandmark,
tresident,CONVERT (varchar(10),tdob) from address where tid=@sid




Update the table with type conversion


create procedure sp_address_update
(
@sid int
,@stitle varchar(10)
,@sname varchar(25)
,@saddress varchar(55)
,@slandmark varchar(25)
,@sresident varchar(10)
,@sdob varchar(10)
)
as begin
update address
set
ttitle=@stitle,
tname=@sname,
taddress=@saddress,
tlandmark=@slandmark,
tresident=@sresident,
tdob=CONVERT(datetime,@sdob,103)
where
tid=@sid

end


Stored Procedure for Insertion

CREATE PROCEDURE sp_Insertion1
(@sempId int,@sempname varchar(50),@sgender varchar(50),@sstreet varchar(50),@semail varchar(50),@spincode varchar(50))
as
SET NOCOUNT ON;
insert into employeear(fempid,fempname,fgender,fstreet,femail,fpincode)
values
(@sempId,@sempname,@sgender,@sstreet,@semail,@spincode)
Multiple Queries in stored procedure
alter procedure sp_insertnew(

@empid int,
@depid int,
@depname varchar(20),
@fname varchar(20),
@lname varchar(20),
@gender varchar(6),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@dateandtime datetime)
as begin

insert into Employenew(
empid,depid,fname,lname,gender,dob,doj,email,city)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city)

insert into hEmployeenew(
empid,depid,fname,lname,gender,dob,doj,email,city,astatus,dateandtime)
values(
@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@dateandtime)

insert into Departnew(
depid,depname)
values(
@depid,@depname)
end