title: How can I fix an "Arithmetic overflow error converting nvarchar to data type numeric.' error?
body: <p>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.</p>
  
  <pre><code>@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
  </code></pre>
  
  <p><strong>This works:</strong></p>
  
  <pre><code>SELECT COUNT(*) as Count FROM A_RECORD_ETH0 WHERE Time_ms = 40388629085.6481
  </code></pre>
  
  <p><strong>This works:</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>This works:</strong></p>
  
  <pre><code>SET @sql = N'SET @Records = (SELECT COUNT(*) FROM '+ @TableName + ' WHERE Time_ms = 40388629085.6481 )'
  
  EXEC sp_executesql @sql, N'@Records int output', @Records output
  </code></pre>
  
  <p><strong>This results in the Arithmetic overflow error:</strong></p>
  
  <pre><code>SET @sql = N'SET @Records = (SELECT COUNT(*) FROM '+ @TableName + ' WHERE Time_ms = ' + @Time_ms + ' )'
  
  EXEC sp_executesql @sql, N'@Records int output', @Records output
  </code></pre>
  
  <p><strong>This results in the Arithmetic overflow error even when cast is used:</strong></p>
  
  <pre><code>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
  </code></pre>