Converting data type nvarchar to int

Question

I have this stored procedure :

CREATE PROCEDURE SearchEmployee
        @EmployeeID int,
        @EmployeeName nvarchar(256),
        @Address nvarchar(256),
AS
    Select 
        EmployeeId, EmployeeName, Address 
    From 
        Employee
    Where
        EmployeeID = @EmployeeID OR
        EmployeeName LIKE '%' + @EmployeeName+ '%' OR
        Address LIKE '%' + @Address+ '%' 

Then in my Winforms I call it like this:

using (SqlCommand mySqlCommand1 = new SqlCommand("dbo.SearchEmployee", myDatabaseConnection))
{
            mySqlCommand1.CommandType = CommandType.StoredProcedure;
            {
                mySqlCommand1.Parameters.AddWithValue("@EmployeeID", textBox1.Text);
                mySqlCommand1.Parameters.AddWithValue("@EmployeeName", textBox1.Text);
                mySqlCommand1.Parameters.AddWithValue("@Address", textBox1.Text);
            }
        }

How I will avoid the error

Error converting data type nvarchar to int.

When a user type a employee name instead of EmployeeID?

I tried this in my stored procedure:

EmployeeID = cast(@EmployeeID as varchar(10))

Answer 1

There is a problem with your design. How can you use same field from Name and Id ? Name can contain any character where as Id can't!. Fastest workaround would be pass invalid value for Id if user types non numeric.

int empId = 0;
if(!int.TryParse(textbox1.Text, out empId))
{
    empId = -1;// or some invalid Id which won't appear in DB
}
mySqlCommand1.Parameters.AddWithValue("@EmployeeID", empId);

this should solve your problem