Chetan Sarode Chetan Sarode - 3 months ago 12
SQL Question

Procedure not creating the number

I need to use this procedure and when I insert the new employee I need to create EmpID in database like A1001,A1002...

But when I using this procedure it shows error to pass EmpID.

Sql Procedure:

ALTER procedure [dbo].[addemp]
@EmpId nvarchar(10),
@EmpName nvarchar(50),
@EmpAddress nvarchar(50)
as
begin
declare @Totalcount int
declare @Count nvarchar(10)

select @Totalcount=(select COUNT(EmpID) from NewEmp)

if @Totalcount is null
set @Count='A'+CONVERT(nvarchar(10),1001)
else
set @Count='A'+CONVERT(nvarchar(10),1001+(@Totalcount))

insert into NewEmp([EmpId],[EmpName],[EmpAddress]) values (@Count,@EmpName,@EmpAddress)
end


C# Code:-

protected void Button1_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["Emp"].ConnectionString;
SqlConnection scon = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("addemp", scon);
cmd.CommandType = CommandType.StoredProcedure;
scon.Open();
cmd.Parameters.AddWithValue("@EmpName",TextBox2.Text);
cmd.Parameters.AddWithValue("@EmpAddress", TextBox3.Text);
cmd.ExecuteNonQuery();
}

Answer

As you are not going to use declared parameter @EmpId in SP, so please remove @EmpId parameter from SP

OR

Set default value : @EmpId=''

create procedure [dbo].[addemp]
    @EmpId nvarchar(10)='',
    @EmpName nvarchar(50)='',
    @EmpAddress nvarchar(50)=''
as 
begin
declare @Totalcount int
declare @Count nvarchar(10)

select @Totalcount=(select COUNT(EmpID) from NewEmp)

if @Totalcount is null
set @Count='A'+CONVERT(nvarchar(10),1001)
else
set @Count='A'+CONVERT(nvarchar(10),1001+(@Totalcount))

insert into NewEmp([EmpId],[EmpName],[EmpAddress]) values (@Count,@EmpName,@EmpAddress)
end