CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Nik Weiss
Nik Weiss

Reputation: 422

Nvarchar value overflowing an int column where there is no int column?

How is my nvarchar value overflowing an int column when I'm not defining any int values?

I'm getting the following error when I run my stored procedure:

The conversion of the nvarchar value '17191925814' overflowed an int column.

The statement I was trying to execute:

EXECUTE [dbo].[updateUser] @status = 'active', ..... ,@srcID = '17191925814'

The problem is that I never define that value as an int. It is always handled as a string, as far as I can tell. In my stored procedure the @srcID parameter is defined as an nvarchar(255):

CREATE PROCEDURE [dbo].[updateUser] ... , @srcID nvarchar(255), ...

And within that stored procedure, I try to UPDATE a value in a column that is also defined as nvarchar(255):

IF @srcID NOT IN ('', '0') AND @srcID IS NOT NULL
    UPDATE [dbo].[Users] SET [Source System ID] = @srcID WHERE ...
ELSE IF @srcID = '0'
    UPDATE [dbo].[Users] SET [Source System ID] = '' WHERE ...

The target table:

CREATE TABLE [dbo].[Users] ( ..., [Source System ID] [nvarchar](255) NULL, ...)

Where, or why, is SQL Server trying to convert '17191925814' to an int, and how can I prevent it?

Edit: I've included the full code of the stored procedure where the parameter @srcID appears.

Upvotes: 2

Views: 3567

Answers (2)

Nik Weiss
Nik Weiss

Reputation: 422

The problem, as pointed out by Damian in the comments, was that I had a trigger active on the database, that was copying that data into an integer column.

So if you have this same issue: check triggers on your database!

Upvotes: 1

etsa
etsa

Reputation: 5060

This doesn't want to be specific answer (I agree with Gordon, Zohar and Damien), but should be difficult to write in a comment. Follow a "reduced" procedure to show the case. I hope it can help you as a method when you have similar problems (always try to reduce the scope), and serve as a confirmation of what Gordon, Zohar and other (I know, there is no need of confirmation as they have great reputation :-) already said:

CREATE TABLE X ( SourceSystemID nvarchar(255) NULL);
INSERT INTO X VALUES ('17191925814');
GO
CREATE PROCEDURE Upd_X @srcID NVARCHAR(255)
AS
BEGIN
IF @srcID NOT IN ('', '0') AND @srcID IS NOT NULL    UPDATE X SET SourceSystemID = @srcID  
ELSE IF @srcID = '0' UPDATE X SET SourceSystemID = '' 
END
;
GO
SELECT * FROM X
EXEC Upd_X '17191925814';
SELECT * FROM X;

Output:

SourceSystemID
---------------
17191925814


SourceSystemID
-----------------------------------------
17191925814

Upvotes: 2

Related Questions