July 6, 2016 at 5:30 am
I have dynamic sql script issue. It is working properly sometimes and giving the error "Arithmetic overflow error converting varchar to data type numeric" sometimes.
When this error occured, I change the Empcode param with single quote then the error is resolved. But I don't understand why it worked for sometimes. Does anybody know the reason why this is happening.
Below are the scripts. I have created the below sample based on my realtime application.
CREATE TABLE [dbo].[Testemp](
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[Empname] [varchar](100) NULL,
[EmpCode] [varchar](50) NULL
) ON [PRIMARY]
Create PROCEDURE [dbo].[Proc_TestEmp] --1,'aaaa',1.0
@EmpId Int,
@Empname Varchar(100),
@EmpCode varchar(50)
AS
Begin
DECLARE @dynSql NVARCHAR(MAX)
--set @dynSql='Update Testemp SET Empname='''+@Empname+''' WHERE EmpId='+ Convert(varchar(20), @EmpId) + ' And EmpCode='''+ Convert(varchar(50),@EmpCode) +''''
set @dynSql='Update Testemp SET Empname='''+@Empname+''' WHERE EmpId='+ Convert(varchar(20), @EmpId) + ' And EmpCode='+ Convert(varchar(20),@EmpCode)
print @dynSql
EXEC sp_executesql @dynSql
End
July 6, 2016 at 5:39 am
Because if you leave out the quotes, then you have something like this:
AND EmpCode = 256781
Where EmpCode is a varchar column and 256781 is a literal with a numeric type. Because they're different types, SQL has to convert of side to do the comparison, and the order of precedence is that strings get converted to int/numeric. Hence SQL is going to do this:
AND CAST(EmpCode AS NUMERIC) = 256781
which will succeed if all values in the column can be converted (which is why it worked in the past) but will fail as soon as there's anything in the EmpCode column which can't be converted to numeric. Oh, and depending which indexes are used you may have it fail or not.
By the way, why is this using dynamic SQL? There's nothing in what you posted that needs dynamic SQL, and the way it's done, there's a serious SQL injection vulnerability.
CREATE PROCEDURE dbo.Proc_TestEmp --1,'aaaa',1.0
@EmpId INT,
@Empname VARCHAR(100),
@EmpCode VARCHAR(50)
AS
BEGIN
UPDATE Testemp
SET Empname = @Empname
WHERE EmpId = @EmpId
AND EmpCode = @EmpCode;
END;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2016 at 5:40 am
tindog (7/6/2016)
In your WHERE clause you convert EmpID to VARCHAR(20), although both the table field and the variable are INT
He's converting it to varchar in order to concatenate it into the dynamic SQL string. There are no quotes around the EmpID, so in the resulting dynamic SQL string, it's an int as is the column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2016 at 5:41 am
GilaMonster (7/6/2016)
tindog (7/6/2016)
In your WHERE clause you convert EmpID to VARCHAR(20), although both the table field and the variable are INTHe's converting it to varchar in order to concatenate it into the dynamic SQL string. There are no quotes around the EmpID, so in the resulting dynamic SQL string, it's an int as is the column.
Got it, thanks bud.
July 6, 2016 at 7:45 am
Hi,
The code i have posted is an example to give the understanding of the queries. In the actual project there is some complex logic in forming the dynamic query.
I have small doubt on the code i have posted. If the EMPcode column value is (1.0 or 2.0 or 3.0...11.0 or 12.0....etc) and if I don't use the single quote in the query is that the expected behaviour of failing after sometime. I am sure that there is no invalid data in the column.
Thanks
July 6, 2016 at 11:16 am
If the column is a varchar, which you said it is, then what you compare against it should be a string, hence in quotes. If you compare a string and an integer SQL has to do conversions unnecessarily, which increases the chance of something breaking.
And there is a value somewhere that can't be cast to numeric, as the error said, "Arithmetic overflow error converting varchar to data type numeric"
And if you need the dynamic SQL, then please, go fix your injection risk before someone exploits it and posts the contents of your database to pastebin.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply