September 12, 2019 at 10:34 am
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
September 12, 2019 at 11:27 am
I suspect that the implicit conversion is causing an issue.
Does this work?
WHERE vaoq.Questionnaire_ID = CONVERT(uniqueidentifier, '09C4C7B4-1275-460A-AE23-FFA9256B1ABE')
September 12, 2019 at 12:28 pm
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
September 12, 2019 at 2:57 pm
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".
September 12, 2019 at 4:08 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy