Error converting int to nvarchar

Question

This error has me very confused. I have a stored procedure with two output parameters as defined below

@agentNumber int OUTPUT,
@currentAgentNum int OUTPUT,

They are then populated using the SELECT statements below

SELECT @agentNumber = AgentNumber
FROM AgentIdentification
WHERE AgentTaxId = @ssn

SELECT @currentAgentNum = AgentNumber
FROM UniqueAgentIdToAgentId
WHERE AgentId = @agentId

In the database, AgentNumber is defined in both tables, as an int. However, when I call this stored procedure in my C# code, I get a SQL exception stating:

Error converting data type int to nvarchar.

If I change the data types of the output parameters to nvarchar, the code will execute, however it will only return nothing more than the first digit of the whole number. Below is how the variables are defined in the C# code

SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", "");//Output parameter - leave blank
SqlParameter outCurrentAgentNumber = new SqlParameter("@currentAgentNum", "");//Output parameter - leave blank

outNewAgentNumber.Direction = ParameterDirection.Output;
outCurrentAgentNumber.Direction = ParameterDirection.Output;

I add these parameters to a SqlCommand object, specify the appropriate database and commandType, then use .ExecuteNonQuery() to call the procedure. Again, what has me really confused is the error message stating that I'm using nvarchar as a data type, which could only (to the best of my knowledge) be referring to something on the database side. However, as I said I've double/triple checked and both AgentNumber columns are of type int.

EDIT

Changing the sqlParameter declarations to a starting value of 0 has solved this issue. I'm now running into the same problem with two other parameters.

SqlParameter outOperator = new SqlParameter("@operator", "");//Output parameter - leave blank
SqlParameter outDate = new SqlParameter("@date", "");//Output parameter - leave blank

Answer 1

Change

SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", "");//Output parameter - leave blank 
SqlParameter outCurrentAgentNumber = new SqlParameter("@currentAgentNum", "");//Output parameter - leave blank 

to

SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", 0);//Output parameter - leave blank 
SqlParameter outCurrentAgentNumber = new SqlParameter("@currentAgentNum", 0);//Output parameter - leave blank 

In your code, you're initially specifying the value as a string because you are passing it a string, which maps to a varchar when going from the CLR type to the SQL Data Type.

Once the runtime sees it as a string, it retains the same type throughout code, so if you assign a numeric (int) value, it still sees it as a string. The trick is to assign it the correct data type in the first place.

Answer 2

These two lines are the culprits:

SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", "");
SqlParameter outCurrentAgentNumber = new SqlParameter("@currentAgentNum", "");

By specifying an empty string ("") as the second parameter, the ADO.NET runtime assumes it's a string paramter. And since you didn't specify any lengths - it probably defaults to just one character length (that's why it's returning only the first digit).

So, my recommendation would be to always explicitly define the datatype (and if it's a string - also define a length!) for your SQL parameters:

SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", SqlDbType.Int);
outNewAgentNumber.Direction = ParameterDirection.Output;
outNewAgentNumber.Value = 0;

or

SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", SqlDbType.NVarChar, 50);
outNewAgentNumber.Direction = ParameterDirection.Output;
outNewAgentNumber.Value = "";