Post reply

Correct use of @variables and uniqueidentifiers

  • January 21, 2009 at 5:40 pm

    #73436

    I get 8 records out of my database when I use this query:

    SELECT email FROM tblExperts WHERE ExpertCategory LIKE '%{FD45C3EE-4A1C-4BF9-8C68-E7085F0EA045}%'

    However, I get zero records out of my database when I attempt to use a @variable:

    DECLARE @catuid uniqueidentifier

    SET @catuid = '{FD45C3EE-4A1C-4BF9-8C68-E7085F0EA045}'

    SELECT email FROM tblExperts WHERE ExpertCategory LIKE @catuid

    ExpertCategory is of type nvarchar(500) because it could contain more than one UID seperated by "|" pipes

    ... ex. {FD45C3EE-4A1C-4BF9-8C68-E7085F0EA045}|{D8A3F2D7-BC99-4D42-AC43-EEA97B057FCE}

    Suggestions?

  • January 21, 2009 at 6:59 pm

    #930833

    You query for different things, so you get different results. Naturally.

    Try this:

    DECLARE @catuid uniqueidentifier

    SET @catuid = '%{FD45C3EE-4A1C-4BF9-8C68-E7085F0EA045}%'

    SELECT email FROM tblExperts WHERE ExpertCategory LIKE @catuid

    or

    DECLARE @catuid uniqueidentifier

    SET @catuid = '{FD45C3EE-4A1C-4BF9-8C68-E7085F0EA045}'

    SELECT email FROM tblExperts WHERE ExpertCategory LIKE '%' + @catuid + '%'

    That's it if to leave crappy table design beyound the scope...

    _____________
    Code for TallyGenerator

  • January 21, 2009 at 7:47 pm

    #930848

    I have to ask, why do you have multiple GUID's in a single column?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 21, 2009 at 8:03 pm

    #930860

    Jeff, it's so obvious! 🙂

    Because:

    - they have list of ExpertCategories in separate table with GUID as a PK (because GUID is so cool and so much more advanced comparing to boring integer IDENTITY )

    - it's so easy and so natural to store all Categories for each expert as a comma separated list in a single field (because it's a list (right?) and it's much more comfortable for a human eye - who cares about those computers, let them do the job!)

    😛

    _____________
    Code for TallyGenerator

  • January 21, 2009 at 8:24 pm

    #930864

    BWAA-HAA!!! Ooooohhh mmmmyyy! Dammit! Stop it, Sergiy... I can't stand laughing this hard! :P:P:P

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 22, 2009 at 7:27 am

    #931161

    Sergiy,

    Thank you for the fine examples you provided.:)

    Your second example is more inline with what I am trying to accomplish:

    DECLARE @catuid uniqueidentifier

    SET @catuid = '{FD45C3EE-4A1C-4BF9-8C68-E7085F0EA045}'

    SELECT email FROM tblExperts WHERE ExpertCategory LIKE '%' + @catuid + '%'

    However, when I run this in SQL Query Analyzer I get the following error message:

    Invalid operator for data type. Operator equals add, type equals uniqueidentifier.

  • January 22, 2009 at 7:31 am

    #931164

    So cast the uniqueidentifier to a varchar before concatenating.

    Why do you have denormalised tables? What's the reason for putting lists into a single column instead of into a child table (where this kind of query is absolutely trivial)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 22, 2009 at 8:01 am

    #931186

    GilaMonster (1/22/2009)

    So cast the uniqueidentifier to a varchar before concatenating.

    Why do you have denormalised tables? What's the reason for putting lists into a single column instead of into a child table (where this kind of query is absolutely trivial)

    Because violations of first normal form are so much fun! And the entertainment value from trying to work out delete and update code for string lists is enough by itself without all the billable hours that adds up to!

    Just think, by violating 1NF, you are almost guaranteeing that some DBA who gets hired after you will be able to retire on the income he makes from your work!

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 22, 2009 at 12:22 pm

    #931383

    PG... although we're having a bit of fun at the expense of your dilema, it would be good to know why (the actual business reason) the table is denormalized the way it is because we actually might be able to help you keep from having such a computational mistake in your database.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 22, 2009 at 12:24 pm

    #931385

    GSquared (1/22/2009)

    Because violations of first normal form are so much fun! And the entertainment value from trying to work out delete and update code for string lists is enough by itself without all the billable hours that adds up to!

    Just think, by violating 1NF, you are almost guaranteeing that some DBA who gets hired after you will be able to retire on the income he makes from your work!

    Heh... Ok, Ok... I just blew a pork chop out of my nose because I'm laughing so hard.

    Just be careful not to scare the new guy away. It would be nice to find out why the poor guy has been given a database with this problem so we can help him fix it in the future.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 22, 2009 at 12:28 pm

    #931387

    Jeff Moden (1/22/2009)

    GSquared (1/22/2009)

    Because violations of first normal form are so much fun! And the entertainment value from trying to work out delete and update code for string lists is enough by itself without all the billable hours that adds up to!

    Just think, by violating 1NF, you are almost guaranteeing that some DBA who gets hired after you will be able to retire on the income he makes from your work!

    Heh... Ok, Ok... I just blew a pork chop out of my nose because I'm laughing so hard.

    Just be careful not to scare the new guy away. It would be nice to find out why the poor guy has been given a database with this problem so we can help him fix it in the future.

    Trust me, I'm not poking any fun at the guy who posted the issue. He's got a mess to deal with, and he need help, not more stress. I'm just being facetious about the reason to do things that way in the first place.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 22, 2009 at 1:46 pm

    #931444

    GilaMonster, GSquared, Jeff Moden,

    You guys are killing me here 😛 !!! I mean, "Pork chop thru the nose", now that's funny :w00t:.

    To quickly end the suspense, I am a less than self-taught SQL noob trying to expand my SQL skillset and allow the database to do as much work as it possibly can. My primary role is developing ASP Intranet Web Applications. Ever since I learned how to create a database-driven web application, I have been using SQL.

    I understand the concepts of database normalization. And, for the most part, I normalize my data. The "string list" column was a result of a conversation with my "AS400" boss. (No, I do not program AS400. I am labeled as a "programmer" and have been attached to the only "programming" group in our company.)

    I was trying to figure out the best way to structure relationships between Categories & Category Experts so I created these tables:

    tblCategories

    cat_id, Category

    tblExperts

    Expert_ID, ExpertName, email, cat_id

    But how do you show the "one to many" relationship when an Expert has more than one Category to their credit? Or when a Category has more than one Expert?

    Do you remove the "cat_id" column from tblExperts and create a whole new table with just these relationships? For example...

    tblExperts

    Expert_ID, ExpertName, email

    tblExpertsCategories

    Expert_ID, cat_id

    Therefore, if Chuck [17] was the Expert of cat_id's [23,35,42] then

    "SELECT Expert_ID, cat_id FROM tblExpertsCategories WHERE Expert_ID = 17" would yield the following result:

    [Expert_ID] [cat_id]

    17 23

    17 35

    17 42

    Is this the preferred method in SQL? Is this now considered to be normalized?

  • January 22, 2009 at 1:56 pm

    #931447

    PG (1/22/2009)

    But how do you show the "one to many" relationship when an Expert has more than one Category to their credit? Or when a Category has more than one Expert?

    Do you remove the "cat_id" column from tblExperts and create a whole new table with just these relationships? For example...

    Yup. It's sometimes refereed to as a many-to-many join. SQL can't create them directly, so you put a table in the middle

    Is this the preferred method in SQL? Is this now considered to be normalized?

    Yes to both.

    With your delimited string, how would you have gone about adding or removing a category?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 22, 2009 at 2:11 pm

    #931464

    For some project scope insight:

    I am trying to develop a "feedback" form when peeps can inform Category specific Experts of a perceived problem along with their suggested solution.

    Users submit their problem/solution for a specific Category via ASP page.

    Problem/Solution data is then stored in SQL database.

    INSERT Trigger is used to "timestamp" when the record was posted.

    UPDATE tblSuggestions SET tblSuggestions.CreateDate

    FROM tblSuggestions INNER JOIN Inserted ON tblSuggestions.Suggestion_ID = Inserted.Suggestion_ID

    Then I want the database to lookup the email address of the Expert(s) based on the Category_ID that was submitted.

    Then I want the database to send an email to these Experts with a static Subject Line and the Perceived Problem and the Suggested Solution in the Body.

    [Currently I get one email per Expert per suggestion; 3 Experts = 3 seperate emails. Would like to know how to loop thru the Experts and concatenate their email addresses to send only one email per suggestion.]

    (To:Expert1@domain.com;Expert2@domain.com;Expert3@domain.com)

    EXEC sp_send_cdosysmail 'FORMS@domain.com',@ExpertEmail,@Subject,@Body

    I can successfully do all of this manually via SQL Query Analyzer. However, I am unable to get this process to complete inside the INSERT Trigger.

    Thus, I have come to you with what I perceived as my delimma.

  • January 22, 2009 at 3:09 pm

    #931518

    To select the experts with a certain code, you could use In, or Join. Either will work.

    In would look like this:

    select Email

    from dbo.Experts

    where ExpertID in

    (select ExpertID

    from dbo.ExpertCategories

    where Category = @Category);

    Join would look like:

    select Email

    from dbo.Experts

    inner join dbo.ExpertCategories

    on Experts.ExpertID = ExpertCategories.ExpertID

    where Category = @Category;

    Either works. I prefer Join for that kind of thing, because you can add more columns to it if you need to.

    There's a trick to string concatenation that's pretty slick, that looks like this:

    declare @To varchar(1000);

    select @To = coalesce(@To + ';' + Email, Email)

    from dbo.Experts

    inner join dbo.ExpertCategories

    on Experts.ExpertID = ExpertCategories.ExpertID

    where Category = @Category;

    Try that out a few times, you'll find it works pretty well.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 21 total)

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

Cookies on SQLServerCentral

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