CODEWITHSUNDEEP
sql-servert-sql
Yan Kleber
Yan Kleber

Reputation: 415

MSSQL: varchar to float conversion error in insert query

This is something that has puzzled me.

I have a SP with a TRY-CATCH and if it fails it should insert a record in an error log table with a message and some codes. This is how it looks like:

INSERT INTO [Errors]
       ([application]
       ,[message]
       ,[extra])
 VALUES
       ('MySP'
       ,ERROR_MESSAGE()
       ,'InvNum=' + str(@inumber) + ' -- InvId=' + str(@IID) + ' -- Error Num: ' + str(ERROR_NUMBER()) + ' -- Error Line: ' + str(ERROR_LINE()))

The problem is that every time it tries to run this query it produces a varchar to float conversion error. It doesn't make sense for me because all the values passed in the last column are being converted to string.

Any idea?

Upvotes: 0

Views: 235

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56745

The problem is that according to the doc, the STR function "Returns character data converted from numeric data". In other words, the parser knows that the STR function requires numeric parameters, so it is trying to convert all of your arguments into floats first and then the STR function will change them into strings.

Personally, I almost never use the STR function because of stuff like this, rather I prefer to use either CAST(.. AS ..) or CONVERT(..) because they 1) care less about the input data type and 2) can produce any output data type based on the parameters. So rather than worry about which function I use to convert from data type (A) to data type (B), I always use the same function (CAST or CONVERT) and just have to worry about getting the parameters right.

Yes, it is true that CAST and CONVERT are a lot clunkier, longer and uglier, but if you just always use CONVERT than you only have to worry about how CONVERT works (also, CONVERT is super-general, you can use it for almost any scalar type conversion).

Upvotes: 1

Daniel Gimenez
Daniel Gimenez

Reputation: 20589

Instead of using the STR function on several values and then concatenating the string with the string concatenation operator, use the CONCAT function. It is pretty flexible with regards to what datatypes you pass into it and it should produce the same expected result.

INSERT INTO [Errors] ([application],[message],[extra])
VALUES (
  'MySP'
  , ERROR_MESSAGE()
  , CONCAT('InvNum=', 
     @inumber, 
     ' -- InvId=', 
     @IID, 
     ' -- Error Num: ',
     ERROR_NUMBER(),
     ' -- Error Line: ',
     ERROR_LINE()
  )
)

Upvotes: 2

Related Questions