SQL Exception: Error converting data type nvarchar to int

Question

Looks like a casting issue, however when casting is applied (to what i believe is the correct format) i still get the SQLException raised in Visual Studio Web Developer 2010 Express (VB.NET)

Start with a Stored Procedure that has 1 input parameter and 4 output parameters and relevant code snippets show below:

ALTER PROCEDURE [dbo].[SelectCompanies] 

@tickersList NVARCHAR(max) = NULL,
@YearInWhichEarningsExistForAllComparisonCompanies int OUTPUT,
@topEndToStartYear nvarchar(9) OUTPUT,
@middleEndToStartYear nvarchar(9) OUTPUT,
@bottomEndToStartYear nvarchar(9) OUTPUT

AS
BEGIN

SET @YearInWhichEarningsExistForAllComparisonCompanies = (SELECT TOP 1 ey.[Year] FROM company c, earningspershare e, earningspershareYear ey WHERE c.ticker_id = e.ticker_id AND e.EarningsPerShareID = ey.EarningsPerShareID AND c.tickerSymbol IN (  SELECT * FROM dbo.fnCSVStringToTable(@tickersList,',')  ) AND ey.Value is not null ORDER by ey.[Year] DESC);

DECLARE @topStartYear int, @topEndYear int;
DECLARE @middleStartYear int, @middleEndYear int;
DECLARE @bottomStartYear int, @bottomEndYear int;


SET @topStartYear = @YearInWhichEarningsExistForAllComparisonCompanies;

SET @topEndYear = @topStartYear - 2;


SET @topEndToStartYear = CAST(@topEndYear As nvarchar(4)) + N'-'  ; --ERROR

....

The relevant VB.NET code is shown below:

    Dim year As New SqlParameter
    Dim topEndToStartYear As New SqlParameter
    Dim middleEndToStartYear As New SqlParameter
    Dim bottomEndToStartYear As New SqlParameter


    'Add tables
    dadCompanies = New SqlDataAdapter("SelectCompanies", conn)
    dadCompanies.SelectCommand.CommandType = CommandType.StoredProcedure

    If Not IsNothing(tBx_c_tickerSymbol.Text.ToString) Then
        dadCompanies.SelectCommand.Parameters.AddWithValue("@tickersList", tBx_c_tickerSymbol.Text.ToString)

        'year Output Parameter
        year = dadCompanies.SelectCommand.Parameters.AddWithValue("@YearInWhichEarningsExistForAllComparisonCompanies", SqlDbType.Int)
        year.Size = 4
        year.Direction = ParameterDirection.Output

        'topEndToStartYear Output Parameter
        topEndToStartYear = dadCompanies.SelectCommand.Parameters.AddWithValue("@topEndToStartYear", SqlDbType.NVarChar)
        topEndToStartYear.Size = 10
        topEndToStartYear.Direction = ParameterDirection.Output

        'middleEndToStartYear Output Parameter
        middleEndToStartYear = dadCompanies.SelectCommand.Parameters.AddWithValue("@middleEndToStartYear", SqlDbType.NVarChar)
        middleEndToStartYear.Size = 12
        middleEndToStartYear.Direction = ParameterDirection.Output

        'bottomEndToStartYear Output Parameter
        bottomEndToStartYear = dadCompanies.SelectCommand.Parameters.AddWithValue("@bottomEndToStartYear", SqlDbType.NVarChar)
        bottomEndToStartYear.Size = 12
        bottomEndToStartYear.Direction = ParameterDirection.Output

    End If

    dadCompanies.Fill(dstCompanies, "company")
....

Points to Note:

The stored procedure when executed from within SQL Server Managment Studio 2012 executes fine, without any errors.
When debugging via Visual Studio Web Developer Express 2010 using IISExpress .NET v2.0.50727 the SQLServer Exception is thrown.
Switching this line in SQL Server CAST(@topEndYear As nvarchar(4)) + N'-' (where the error is occuring) to N'-' + CAST(@topEndYear As nvarchar(4)) CORRECTS the problem, i.e. leading with a nvarchar character and then casting
In VB.NET changing the value SqlDbType.NVarChar to SqlDbType.NVarChar.ToString CORRECTS the problem if i leave the problem line as CAST(@topEndYear As nvarchar(4)) + N'-'

I would really like to understand why this is happening. Even though i have some solutions they dont give me an explanation of why. I thought i had a good grasp on Casting, maybe there is something i am missing?

Will people never learn not to store numeric data in character columns... — jarlh, Sep 29 '15 at 12:58
The keyword is **[Data Type Precedence](https://msdn.microsoft.com/en-us/library/ms190309.aspx)** — Lukasz Szozda, Sep 29 '15 at 13:03
You should [read the documentation](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx) of the functions you are using... — the_lotus, Sep 29 '15 at 13:34

score 1 · Accepted Answer · answered Sep 29 '15 at 13:03

If you use AddWithValue the second parameter is the value but you pass the SqlDbType. Instead use Add. This is one of the reasons which speak against AddWithValue:

'year Output Parameter
Dim Year = dadCompanies.SelectCommand.Parameters.Add("@YearInWhichEarningsExistForAllComparisonCompanies", SqlDbType.Int)
Year.Size = 4
Year.Direction = ParameterDirection.Output

'topEndToStartYear Output Parameter
Dim topEndToStartYear = dadCompanies.SelectCommand.Parameters.Add("@topEndToStartYear", SqlDbType.NVarChar)
topEndToStartYear.Size = 10
topEndToStartYear.Direction = ParameterDirection.Output

'middleEndToStartYear Output Parameter
Dim middleEndToStartYear = dadCompanies.SelectCommand.Parameters.Add("@middleEndToStartYear", SqlDbType.NVarChar)
middleEndToStartYear.Size = 12
middleEndToStartYear.Direction = ParameterDirection.Output

'bottomEndToStartYear Output Parameter
Dim bottomEndToStartYear = dadCompanies.SelectCommand.Parameters.Add("@bottomEndToStartYear", SqlDbType.NVarChar)
bottomEndToStartYear.Size = 12
bottomEndToStartYear.Direction = ParameterDirection.Output

SQL Exception: Error converting data type nvarchar to int

1 Answers1