wierd type conversion error

  • MVDBA

    SSC-Insane

    Points: 20728

    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

  • DesNorton

    SSC-Insane

    Points: 22645

    I suspect that the implicit conversion is causing an issue.

    Does this work?

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

    Old Hand

    Points: 377

    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;
  • MVDBA

    SSC-Insane

    Points: 20728

    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

  • ScottPletcher

    SSC Guru

    Points: 98087

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • MVDBA

    SSC-Insane

    Points: 20728

    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 6 (of 6 total)

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