Datatype conversion strangeness

Hot Topic

How to restrict subreport height?
MDX Query designer working correctly?
Dynamic Snapshot Agent Error
How to reference different databases on different servers in one query
Avoiding deadlock
article for subscription that does not exist ?!?
SQL 2000 Program Installation location for performance.
return xml from .net strored procedure
Row and column count in SSMS
Changing Ordering of a Dimension Attribute

SQL Server

Set amounts to zero in addition if no records found
SQL Agent Failing when trying to run my SSIS Package
Basic DB Question
SQL Server Management Studio
Failed to generate a user instance of SQL Server
Hide matrix rows?
Changing column data type constraint
report server compatability issues
Bookmark Lookup
Problem translating schedule into 1/2 hour buckets

Datatype conversion strangeness

Hi all,


After more headbanging and cursing than an entire Metallica audience, I have finally deduced how to store and calculate the values of a stack storing RPN calculations using a recursive stored procedure. But there is a new conundrum. Originally, to test the above, I was using fairly simple values -- 10, 20, +, 3, /, etc. But the real data is likely to include values with at least a couple of decimal places -- these have been configured using the money data type. Nevertheless, in my actual stack table the value needs to be specified as a varchar. However, as soon as I start sending in 0.68 as a varchar to my stored procedure, it gives an error.

Can anyone offer any light on this

(The error message number is 245, data type conversion error. But this is inexplicable as I have commented out any conversion code in order to get to the root of the problem. As far as I am aware this value "0.68" is always being passed around as a varchar.)

Thanks.

Answer this question

Datatype conversion strangeness

  • Annie L

    OK let me try to explain as fully as possible.

    I have a table NEO_FORMULA_STACK (f_id(int), position(int), val(varchar(100)), valname(varchar(100)), units (nvarchar(12).

    I have a stored proc, which accepts f_id (as above) as a parameter. Based on this f_id it then loads the 'stack' of values into table TEMP_STACK (pos(int),val(varchar(100).

    What I mean is that there are multiple formulas in the original table each with their own 'stack' of values (arranged in Reverse Polish Notation format, i.e. 10,20,+,3,/ [a way of writing ((10+20)/3).

    Now, in the course of the load from NEO_FORMULA_STACK TO TEMP_STACK conversions such as tablename.objectname being evaluated to e.g. 0.098 might very well occur. But my impression is that if NEO_FORMULA_STACK has a val column of VARCHAR(100) datatype, and so does TEMP_STACK, then there should be absolutely no problem going from 'tablename.objectname' to '0.000918'. These are BOTH completely valid strings. Bear in mind that NO calculation has taken place at this point, I have simply loaded NEO_FORMULA_STACK for a particular f_id into TEMP_STACK. Attempting to pop off a value like '0.0098' into a temporary variable of varchar(100) is throwing error: 245, converting from varchar to int at line 77 of the stored proc:-

    I can't give you the exact line, because one sp is calling another so the line it cites as being in the wrong is a comment.

    Please let me know if you need more detail, or if you have any clues based on this information. Many thanks!

  • Andre Barendse

    Again, it is hard to tell what is wrong without looking at some piece of code that reproes the error. Alternatively, you could run profiler trace with statement level events to see which statement is failing (you can even get this from the error message header which will point to the line that generated the error).

  • Greg Lee

    "But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors."

    I assure you I am doing no concatenation or anything like that. I removed all such code in order to try and attack the problem. I am loading one varchar into another varchar, then popping that varchar off into a temporary variable of type varchar -- and it is that pop which is throwing the error, saying illegal conversion, when as far as I can see they are ALL varchar(100).

  • Erzeel

    But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors. It is hard to tell what is wrong without looking at some repro. You should however avoid doing these type of operations since the potential for misinterpretation or run-time error is large. If you are dealing with numeric values then specify the data type of the variables accordingly.

  • paulxray

    Your explanation doesn't really help to clarify the exact operations being done in the SP. As I said, this error happens due to implicit conversions of values of different types or initialization of variables/parameters using value of different data type. Add additional debug statements to see the results before the input so you can identify the issue. Or create a simpler repro that will help you identify the problem due to implicit conversion.

  • mluloh

    Umachandar,

    Many thanks for your reply. The reason I'm doing it this way is that I'm modelling a stack. Also some values are actually the tablename.objectname id of values in a read-only section of the database, so the <value> part of this stack has to be flexible -- hence varchar. Surely the potential for runtime error is not that great, as I only want to be able to handle values such as the following:-

    table1.id1 (this gets parsed by the stored procedure into values such as follow...)
    0.68
    0.43
    10
    20
    +
    *
  • Datatype conversion strangeness
Answer this question