SQL Server 2008: Error converting data type nvarchar to bigint when not converting anything

Question

I am getting an error

Error converting data type nvarchar to bigint

when I am not converting anything whatsoever. All I am doing is trying to insert a new record. The stored procedure is shown below and if I take away my bigint params, it works flawlessly. This is also being tested in SQL Server Management Studio, so any code flaws on my end is not the problem.

ALTER PROCEDURE dbo.sp_generateTestTable
    @suiteID AS bigint,
    @testXml AS nvarchar(128),
    @platXml AS nvarchar(128),
    @table AS nvarchar(32)

    DECLARE @QUERY VARCHAR(2000) = ''
    SET @QUERY = 'INSERT INTO SACT.dbo.' + @table + '(BUILD_ID, SUITE_ID, ISAUTOMATED, DATESTARTED, TESTXML, PLATFORMXML)
 VALUES (3568, ' + @suiteID + ', 1, GETDATE(), ''' + @testXml + ''', '''  + @platXml +''' ) '

    EXECUTE (@QUERY)

So like I said, I am testing this from SQL Server Management Studio using

DECLARE @return_value int

EXEC    @return_value = dbo.sp_generateTestTable
        @testXml = N'mytest.xml'
        ,@platXml = N'myplatform.xml'
        ,@table = N'BoulderHMI'
        ,@suiteID = 13

So that no code on my end is responsible for sending a string where an int should be or vice versa. Like before, if I take out the @SuiteID param like I did the BuildID param, it will work flawlessly. Why it thinks the 13 being passed in is a string, I have no clue.

Sean Lange Sean Lange · Accepted Answer · 2016-02-09T21:24:48

The problem is you have to cast/convert your @SuiteID to nvarchar. The way this is coded it will do an implicit conversion to the highest order datatype which in this case would be int. It is trying to do math against string values.

The fix to your code would be this.

, ' + CAST(@suiteID as nvarchar(4)) + '

SQL Server 2008: Error converting data type nvarchar to bigint when not converting anything

1 Answers