SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  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 Inserting a record

    hello
    I have this Stored Proceedure that inserts up to 10 entries in my db, based on the users entries.

    Its a phone book entry, however i would like to confirm before entry that the user hasnt entered the phone number before running the insert. Please correct me in the code below

    DO I USE AN IF EXISTS or just leave it the way it is ?

    thanks
    Afrika

    Code:
    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)
    ... code continues till 10

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the best way to do this is not to check first

    instead, just go ahead and insert, and let the database tell you whether or not that entry exists already

    this is controlled by declaring a unique constraint on the username/number
    rudy.ca | @rudydotca
    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)
    No I cant use a unique constraint, cos its a phone book, with the PK as the username and different users could have the same phone number.

    I also cant index them for this reason

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, you can, you can declare a unique constraint on the pair of columns!

    what you are trying to control is whether this user has entered this number before

    you can also index the pair
    rudy.ca | @rudydotca
    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)
    Sounds interesting, i have never done this before, I thought SQL server would throw an error if a unique constraints exists and disallows duplicates.

    So how could this be done efficiently ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's exactly what you are looking for -- an error that means "you have attempted to insert a duplicate"

    one database call (INSERT) is always more efficient than two (SELECT + INSERT)
    rudy.ca | @rudydotca
    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)
    I dont understand your approach.

    The uniqueID here is the username, and each user can have multiple phone book entries. Howevr a user e.g.
    Rudy and Afrika could have the same phone number, ie we could both know the same person.

    Now if i do create a unique constraint, it would disallow duplicates.

    Do you understand ?

    Thats why i am implementing a select first before an insert.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afrika
    I dont understand your approach.
    that's right, you don't

    here, let me see if i can make it more clear

    the unique constraint is on a pair of columns

    like this --
    Code:
    create table [1day_com_sql].[dbo].[sms_phonebook] 
    ( username      varchar(37)
    , Phone_number  varchar(37)
    , Nickname      varchar(37)
    , constraint no_dupe_user_number_combo
        unique (username,Phone_number)
    )
    so even though two people could have the same number, the same person cannot have the same number more than once

    hence, just do the INSERT -- no need to do the SELECT first

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

  9. #9
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you got me right,

    the reason for the select is because. Its a SP, that does an insert for up to 10 entries. And if one fails then all fail, so if a duplicate exists in entry 6 or so down the line, it would either roll back all 10 entries or disallow the other entries.

    Thanks for your advice :-)
    Afrika

  10. #10
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    code is like this

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO



    ALTER PROCEDURE [dbo].[insert_sms_phonebook_1]
    (@username_1 [varchar](20),
    @Phone_number_1 [int],
    @Nickname_1 [varchar](50),
    @Nickname_2 [varchar](50),
    @Phone_number_2 [int],
    @Nickname_3 [varchar](50),
    @Phone_number_3 [int],
    @Nickname_4 [varchar](50),
    @Phone_number_4 [int],
    @Nickname_5 [varchar](50),
    @Phone_number_5 [int],
    @Nickname_6 [varchar](50),
    @Phone_number_6 [int],
    @Nickname_7 [varchar](50),
    @Phone_number_7 [int],
    @Nickname_8 [varchar](50),
    @Phone_number_8 [int],
    @Nickname_9 [varchar](50),
    @Phone_number_9 [int],
    @Nickname_10 [varchar](50),
    @Phone_number_10 [int]
    )

    AS 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

    select
    @username_1,
    @Phone_number_3,
    @Nickname_3
    where @Phone_number_3 is not null and @phone_number_3 <> '23480'


    union all

    select
    @username_1,
    @Phone_number_4,
    @Nickname_4
    where @Phone_number_4 is not null and @phone_number_4 <> '23480'

    union all

    select
    @username_1,
    @Phone_number_5,
    @Nickname_5
    where @Phone_number_5 is not null and @phone_number_5 <> '23480'


    union all

    select
    @username_1,
    @Phone_number_6,
    @Nickname_6
    where @Phone_number_6 is not null and @phone_number_6 <> '23480'



    union all

    select
    @username_1,
    @Phone_number_7,
    @Nickname_7
    where @Phone_number_7 is not null and @phone_number_7 <> '23480'



    union all

    select
    @username_1,
    @Phone_number_8,
    @Nickname_8
    where @Phone_number_8 is not null and @phone_number_8 <> '23480'



    union all

    select
    @username_1,
    @Phone_number_9,
    @Nickname_9
    where @Phone_number_9 is not null and @phone_number_9 <> '23480'




    union all

    select
    @username_1,
    @Phone_number_10,
    @Nickname_10
    where @Phone_number_10 is not null and @phone_number_10 <> '23480'




    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, good luck with that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks a lot


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
  •