wierd type conversion error

Question

Post reply

wierd type conversion error

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
September 12, 2019 at 10:34 am

#3679425
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

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

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

I suspect that the implicit conversion is causing an issue.

Does this work?

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

How to post data/code on a forum to get the best help.

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

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: 101095 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

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