wierd type conversion error

  • Hi

    I've been tracking through some code that has a type conversion error from varchar to uniqueidentifier

    I started commenting out parts of the code and found the offending line of code

    WHERE vaoq.Questionnaire_ID = '09C4C7B4-1275-460A-AE23-FFA9256B1ABE'

    I checked that this is a valid GUID (using convert)

    the only way I managed to get this fixed was to create a table variable and do this

     DECLARE @r TABLE
    (id uniqueidentifier)
    INSERT INTO @r SELECT '09C4C7B4-1275-460A-AE23-FFA9256B1ABE'

    and change my where clause to

    WHERE vaoq.Questionnaire_ID IN (SELECT id FROM @r)

    has anyone seen this before and can point me at anything online.. i'm 100% bamboozled as to why the "in" does not cause type confusion, but an equality statement does

     

    MVDBA

  • I suspect that the implicit conversion is causing an issue.

    Does this work?

    WHERE vaoq.Questionnaire_ID = CONVERT(uniqueidentifier, '09C4C7B4-1275-460A-AE23-FFA9256B1ABE')
  • This works:

    declare @v table(
    varchar_id varchar(36));
    declare @u table(
    unique_id uniqueidentifier);

    insert @v(varchar_id) values('09C4C7B4-1275-460A-AE23-FFA9256B1ABE');

    insert @u(unique_id) select * from @v where varchar_id='09C4C7B4-1275-460A-AE23-FFA9256B1ABE';

    select * from @v v join @u u on v.varchar_id=u.unique_id;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • DesNorton wrote:

    I suspect that the implicit conversion is causing an issue.

    Does this work?

    WHERE vaoq.Questionnaire_ID = CONVERT(uniqueidentifier, '09C4C7B4-1275-460A-AE23-FFA9256B1ABE')

     

    that was the first thing I tried

    MVDBA

  • Something else is going on, would need to see a more complete original statement and the table definition(s).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher wrote:

    Something else is going on, would need to see a more complete original statement and the table definition(s).

    sorry, that proc is huge and references views which have functions etc etc (it was like that when i got here)

    thankfully there are no triggers or cursors!!!!

    i'm going to rule it down to the query optimiser not liking a hard coded value.... but it's annoying me why "IN" works but "=" doesn't

     

    MVDBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply