SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Check for an entry before Insert and Union clause

    hello,
    I have the script below, that is from a SP, which checks for an entry before doing an insert.

    It works with a union clause allowing up to 10 records to be inserted.

    how do i check to see if the phone number and the nickname already exists ?

    thanks
    Afrika
    Code:
    AS 
    If exists (select * from sms_phonebook where phone_number = @phone_number_1 and username = @username_1)
    else
    INSERT INTO [1day_com_sql].[dbo].[sms_phonebook] 
    ([username],
    [Phone_number],
    [Nickname]) 
    select 
    @username_1,
    @Phone_number_1,
    @Nickname_1
    
    where @Phone_number_1 is not null and @phone_number_1 <>  '23480' and @phone_number_1 <> 
    (select @Phone_number_1 from sms_phonebook where username = @username_1)
    union all
    
    select
    @username_1,
    @Phone_number_2,
    @Nickname_2
    where @Phone_number_2 is not null and @phone_number_2  <> '23480' and @phone_number_2 <> 
    (select @Phone_number_2 from sms_phonebook where username = @username_1)
    
    union all

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by afrika
    how do i check to see if the phone number and the nickname already exists ?
    don't

    declare UNIQUE constraints and let the database do the work

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont understand how i could declare a unique key. Although i did on the username. Which is unique

    The phone numbers cant be unique, cos afrika and rudy both usernames could have the same phone numbers

    Seconldy, if i do declare unique keys, and there is an error, the whole insert woudl fail ? and roll back all transactions right ?

    Am i missing somehting ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    are you missing something? yes, we covered this before

    (i'm too lazy to look it up, but you may)

    declare the constraint on the pair of columns
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    (i'm too lazy to look it up
    1st error statement coming from you.
    :-)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, it's quite true

    i am too lazy to look it up for you

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, it's quite true

    i am too lazy to look it up for you
    your statement has just thrown and error and this forum will be terminate.

    LAZY CANT BE in your dictionary,

    ...Oh DB guru
    END

    am out of here

    EXIT FOR....

    Afrika leaves sitepoint.com..........................................


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •