Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    UK
    Posts
    1

    Unanswered: 70-229 Microsoft example

    I am studying for the 70--229 microsoft exam using the MS study guide. An example of a stored procedure used to add customer and check for dupes is below. However although the dupelicatecheck sp is called duplicate contact's can be added. Can anyone give me advise on where I may have gone wrong with this example.

    CREATE procedure dbo.addcustomer
    @firstname varchar(30)='Unknown',
    @lastname varchar(30)='Unknown', @Phone varchar(24)= NULL,
    @Address1 varchar(60)= NULL,
    @address2 varchar(60)='unknown', @city varchar(15)=Null,
    @state varchar(7)=Null, @zip varchar(12)=NULL
    as
    IF (@Firstname='Unknown') and (@Lastname='Unknown')
    return(1)

    Else IF @phone is null
    return(2)
    Else IF
    @Address1 is null or @city is null or
    @state is null or @zip is null
    Return(3)
    --begin nesting
    declare @r_code int, @v_firstname varchar(30),
    @v_lastname varchar(30), @v_city varchar(15),
    @v_state varchar(7), @v_phone varchar(24)
    execute @r_code=dbo.checkforduplicatecustomer
    @1_firstname=@firstname, @1_lastname=@lastname,
    @1_city=city, @1_state=@state, @1_phone=@phone,
    @o_firstname=@v_firstname output,
    @o_lastname=@v_lastname output, @o_city=@v_city output,
    @o_state=@v_state output, @o_phone=@v_phone output
    if @@rowcount>0
    begin
    Print'A duplicate record was found for' + @v_firstname+' ' +
    @v_lastname
    print 'in' +@v_city + ' '+ @v_state + ' with a phone number'
    print 'of' + @v_phone +','
    return(5)
    end
    --end nesting
    insert [bookshopdb].[dbo].[customers]
    (firstname, lastname, phone,
    address1, address2, city, state, zip)
    values
    (@firstname, @lastname, @phone,
    @address1, @address2, @city, @state, @zip)
    Return(select @@identity as 'identity')
    if @@error <>0
    return (4)



    create procedure dbo.checkforduplicatecustomer
    @1_firstname varchar(30)='Unknown',
    @1_Lastname varchar(30)='Unknown',
    @1_City varchar (15)= Null, @1_state varchar(7)= null,
    @1_phone varchar(24)= null, @o_firstname varchar (30) output,
    @o_lastname varchar(30) output, @o_city varchar (15) output,
    @o_state varchar(7) output, @o_phone varchar(24) output
    as
    select @o_firstname=firstname, @o_lastname=lastname,
    @o_city=city, @o_state=state, @o_phone=phone
    from customers
    where firstname=@1_firstname and lastname=@1_lastname
    and city=@1_city and state=@1_state and phone=@1_phone
    if @@rowcount <> 0
    return (5)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You assign the output of checkforduplicatecustomer to variable @r_code, but your main procedure checks that value of if @@rowcount to determine whether to generate a duplicate record error. Shouldn't you be checking @r_code instead?

    Also, you would be better off wrting your checkforduplicatecustomer procedure as a user-defined function. They are much easier to use in code, like this:
    if dbo.checkforduplicatecustomer(Parameters...) = 1 begin.....

    And why don't you just check for duplicates in your main code using IF EXISTS? That would be the simplest way, unless you really need to reuse the duplicate checking logic in other procedures as well.

    blindman

Posting Permissions

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