Procedure to Insert EmployeeId Automatically incremented by one based on DepartmentID

This is my table.

create table tbl_employee

(

empno varchar(10) primary key,

empname varchar(40),

deptid varchar(10),

designation varchar(40),

salary int

)

This is my procedure. It will insert Empno automatically increased by one based on deptid.

i.e. For deptA, empno will be deptA,deptA2,deptA3,……

For deptB, empno will be deptB1,,deptB2,deptB3,……

create proc InsertToTable

(@eName varchar(40),@eDeptId varchar(20),@eDesignation varchar(40),@eSalary int)

as

begin

declare @count varchar(10);

set @count=(select COUNT(designation) from tbl_employee where designation=@eDesignation)+1

insert into tbl_employee values (@eDesignation+@count,@eName,@eDeptId,@eDesignation,@eSalary)

end

Good luck………….

 

Procedure with output parameter in ASP.NET

My Procedure is follows,

create proc outparam(@a int,@b int,@c int out)

as

set @c=@a+@b;

In Code behind,

SqlCommand cmd = new SqlCommand(“outparam”, con);

con.Open();

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(“@a”, SqlDbType.Int).Value = TextBox1.Text;

cmd.Parameters.Add(“@b”, SqlDbType.Int).Value = TextBox2.Text;

cmd.Parameters.Add(“@c”,SqlDbType.Int);

cmd.Parameters[“@c”].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

TextBox3.Text = cmd.Parameters[“@c”].Value.ToString();

con.Close();

Good luck……………..