CODEWITHSUNDEEP
sqlsql-servermathcastingoverflow
user3023542
user3023542

Reputation: 1

How can I fix an "Arithmetic overflow error converting nvarchar to data type numeric.' error?

I have a simple stored procedure where the target table and where clause condition are specified as parameters. The "Arithmetic overflow error converting nvarchar to data type numeric." error is causing me a bit of frustration.

@TableName varchar(50), -- 'A_RECORD_ETH0' test value

@Time_ms decimal(18,4), -- '40388629085.6481' test value

@Records int out,       -- should result with '1' if test values are used

This works:

SELECT COUNT(*) as Count FROM A_RECORD_ETH0 WHERE Time_ms = 40388629085.6481

This works:

SET @sql = N'SET @Records = (SELECT COUNT(*) FROM A_RECORD_ETH0 WHERE Time_ms = 40388629085.6481 )'

EXEC sp_executesql @sql, N'@Records int output', @Records output

This works:

SET @sql = N'SET @Records = (SELECT COUNT(*) FROM '+ @TableName + ' WHERE Time_ms = 40388629085.6481 )'

EXEC sp_executesql @sql, N'@Records int output', @Records output

This results in the Arithmetic overflow error:

SET @sql = N'SET @Records = (SELECT COUNT(*) FROM '+ @TableName + ' WHERE Time_ms = ' + @Time_ms + ' )'

EXEC sp_executesql @sql, N'@Records int output', @Records output

This results in the Arithmetic overflow error even when cast is used:

SET @sql = N'SET @Records = (SELECT COUNT(*) FROM '+ @TableName + ' WHERE Time_ms = Cast ( ' + @Time_ms + ' as decimal(18,4)) )'

EXEC sp_executesql @sql, N'@Records int output', @Records output

Upvotes: 0

Views: 6452

Answers (2)

user3023542
user3023542

Reputation: 1

I got the following to work based on your code:

DECLARE @sqlSelect nvarchar(500);
DECLARE @ParmSelect nvarchar(500);
SET @sqlSelect = N'SET @RecordsOut = (SELECT COUNT(*) FROM ' + @TableName + ' WHERE Time_ms = @Time_msIn)'
SET @ParmSelect = N'@Time_msIn decimal(18,4), @RecordsOut int OUTPUT';
EXEC sp_executesql @sqlSelect, @ParmSelect, @Time_msIn = @Time_ms, @RecordsOut = @Records OUTPUT;

Upvotes: 0

Anon
Anon

Reputation: 10908

Passing @Time_ms directly avoids the decimal->string->decimal conversion. It's also more efficient; when @Time_ms changes, the server can still reuse the execution plan instead of generating a new one.

SET @sql = N'SET @Records = (SELECT COUNT(*) FROM '+ @TableName + ' WHERE Time_ms = @Time_ms'

EXEC sp_executesql @sql, N'@Time_ms decimal(18,4), @Records int output', @Time_ms=@Time_ms, @Records output

Upvotes: 1

Related Questions