Comparing a GUID in a where clause

[meckeard]

Hi,

I have a table that needs a unique ID (string) for each record. So I used newid() and it gets auto generated upon each insert into my table.

Now, when I need to find a record using this value in my select statement in a sproc, it just hangs.

Here is the select:

@Confirmation_Number varchar(250),
@Email varchar(100)

as

select @Member_ID = Member_ID
from FG_Members
where
Confirmation_Number = @Confirmation_Number
and Email = @Email

Is there a special way to handle GUID comparisons in a where clause in a sproc?

Thanks,
Mark

 

[dmcmunn]

declare @sql

set @sql = 'select Member_ID from FG_Members where Confirmation_Number = ''' + @Confirmation_Number + '''' + ' and Email = ''' + @Email + ''''

exec (@sql)

 

[meckeard]

dmcmunn,

Same results. It just hangs.

BTW, I don't see any difference between my code & yours except for the fact that yours is executing the string built dynamically. So why would it work differently?

Thanks,
Mark

 

[Transcend]

You need the single quotes around the GUID

so your query would look something like

select 1 = Member_ID
from FG_Members
where
Confirmation_Number = '123-456-ret'
and Email = 'test@email.com'

(i know thats not a real GUID but i was just illustrating a point)

you don't have the single quotes ' around your variables.

Transcend
[goregous]

 

[Transcend]

i mean

 

[ArtieChoke]

You need the single quotes around the GUID

Not when using variables, as in the first example.

I'm assuming you left off the create procedure statement from the code example. If not, that could be the problem. When you say it just hangs, do you mean the query never completes and you finally kill it?

How big is the table it's searching and are they indexes on those fields in the where clause? Please post your entire sp, as the part above is missing declarations and it's not clear if you just left those pieces out for brevity.

Also, if you run it as a select rather than in an sp, does it work that way?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[meckeard]

AriteChoke,

Yes, it just runs until I kill it.

The table has 1 record, no indexes.

I did find the solution.

In the database, I made the datatype for the field a varchar instead of a uniquindentifier. After changing it, it worked.

I must admit, I am at a loss to explain why. Not that it matters, but out of curiosity, it would be nice to know the cause.

Thanks all.

Mark

 

[ashstampede]

what happens is this, you created a column named unique id as varchar, but when you use the newid() it will change your varchar data type value to a uniqueidentifier type and no longer a varchar. so as you found uniqueidentifier compare to uniqueidentifer its's all happy go lucky.

a unquieidentifier type can't be cast to a string you'll get an error on that, so keep that in mind on front end apps adding and comparing values in your database.
In my sql script i used varchar type and used the convert function

set @id = convert(varchar(255),newid())

when i was assiging the GUID value to my column.

hopes that makes sense.