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)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

70-228 Exam Book?

Hello
Can anyone suggest a good book for preparing 70-228? I found this
MCSA/MCSE/MCDBA: SQL Server 2000 Administration Study Guide (70-228) by
Sybex pub. But I am not sure about this really. Someone was really
praising Wrox - Professional SQL Server 2000 Programming. I have no
previous experience of SQL Server 2000, but I am comfortable with
programming and SQL language.
Thanks for your suggestions in advance.
Kappa
Hi
You may want to check out http://www.microsoft.com/sql/techinfo/books.mspx
you may also want to check out the (SQL 2005) e-learning courses at
https://www.microsoftelearning.com/c...aspx#sqlServer which are
currently being offered for free (till Nov 2006).
John
"Trying2Learn" wrote:

> Hello
>
> Can anyone suggest a good book for preparing 70-228? I found this
> MCSA/MCSE/MCDBA: SQL Server 2000 Administration Study Guide (70-228) by
> Sybex pub. But I am not sure about this really. Someone was really
> praising Wrox - Professional SQL Server 2000 Programming. I have no
> previous experience of SQL Server 2000, but I am comfortable with
> programming and SQL language.
>
> Thanks for your suggestions in advance.
>
> Kappa
>

70-228 Exam Book?

Hello
Can anyone suggest a good book for preparing 70-228? I found this
MCSA/MCSE/MCDBA: SQL Server 2000 Administration Study Guide (70-228) by
Sybex pub. But I am not sure about this really. Someone was really
praising Wrox - Professional SQL Server 2000 Programming. I have no
previous experience of SQL Server 2000, but I am comfortable with
programming and SQL language.
Thanks for your suggestions in advance.
KappaHi
You may want to check out http://www.microsoft.com/sql/techinfo/books.mspx
you may also want to check out the (SQL 2005) e-learning courses at
https://www.microsoftelearning.com/...aspx#sqlServer which are
currently being offered for free (till Nov 2006).
John
"Trying2Learn" wrote:

> Hello
>
> Can anyone suggest a good book for preparing 70-228? I found this
> MCSA/MCSE/MCDBA: SQL Server 2000 Administration Study Guide (70-228) by
> Sybex pub. But I am not sure about this really. Someone was really
> praising Wrox - Professional SQL Server 2000 Programming. I have no
> previous experience of SQL Server 2000, but I am comfortable with
> programming and SQL language.
>
> Thanks for your suggestions in advance.
>
> Kappa
>

70-228 Exam Book?

Hello
Can anyone suggest a good book for preparing 70-228? I found this
MCSA/MCSE/MCDBA: SQL Server 2000 Administration Study Guide (70-228) by
Sybex pub. But I am not sure about this really. Someone was really
praising Wrox - Professional SQL Server 2000 Programming. I have no
previous experience of SQL Server 2000, but I am comfortable with
programming and SQL language.
Thanks for your suggestions in advance.
KappaHi
You may want to check out http://www.microsoft.com/sql/techinfo/books.mspx
you may also want to check out the (SQL 2005) e-learning courses at
https://www.microsoftelearning.com/catalog/itproDev.aspx#sqlServer which are
currently being offered for free (till Nov 2006).
John
"Trying2Learn" wrote:
> Hello
>
> Can anyone suggest a good book for preparing 70-228? I found this
> MCSA/MCSE/MCDBA: SQL Server 2000 Administration Study Guide (70-228) by
> Sybex pub. But I am not sure about this really. Someone was really
> praising Wrox - Professional SQL Server 2000 Programming. I have no
> previous experience of SQL Server 2000, but I am comfortable with
> programming and SQL language.
>
> Thanks for your suggestions in advance.
>
> Kappa
>