|
Home > Archive > SQL server exams > July 2002 > Error Handling
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| cocolocopolo 2002-07-01, 12:54 am |
| From "Microsoft SQL Server 2000 Database Design & Implementaton Training Kit", there is a training question to create a database
(by the name of BookShopDB) and several tables, one of the table is Customers (but I created tblCustomers at that time).
Now, when it comes to Chapter 8 "Implementing Stored Procedures"
to practise Error Handling, I followed the instruction, entered the following code:
Declare @r_Code int
Execute @r_Code=dbo.AddCustomer
@FirstName= 'Jamie', @LastName= 'Marra',
@Phone = '425-555-1212', @Address1 = '20 Oak St., SE',
@City = 'Remy', @State = 'WA', @Zip = '98888'
If @r_Code = 4
Begin
Print 'A database error has occured.
Please contact teh help desk for assistance.'
End
If @r_Code = 1
Print 'You must specify a value for the firstname or lastname.'
Else If @r_Code = 2
Print 'You must specify a value of the phone number.'
Else If @r_Code = 3
Print 'You must provide all address information, Street address, City, State and Zipcode.'
Else If @r_Code = @@Identity
Select [Customer ID] = 'The new customer is: ' +
Convert(Char(2), @r_Code)
But I got error:
Server: Msg 208, Level 16, State 1, Procedure AddCustomer, Line 16
Invalid object name 'BookShopDB.dbo.Customers'.
I understand there is no such table by the name of Customers (only tblCustomers).
So I modified the codes and enter following code instead:
Select [BookShopDB].[dbo].[tblCustomers].[Customer ID]= 'The new Customer ...'
But got another error again that:
Line 23: Incorrect syntax near '='.
Besides drop the tblCustomers, then re-create Customers table, I wonder if there is another way to fix this error???
Please help. | |
| 2lazybutsmart 2002-07-01, 12:58 am |
| looks like the error is comming from the dbo.AddCustomer stored proc. can u print that out for us here on the thread so we can analyze it a bit.
i hope ur not facing role problem, i hate them. | |
| kingsunl 2002-07-02, 11:18 am |
| quote:
Select [BookShopDB].[dbo].[tblCustomers].[Customer ID]= 'The new Customer ...'
When specifying aliases, the correct syntax is:
Select 'The new Customer'=[BookShopDB].[dbo].[tblCustomers].[Customer ID],...
Place the alias first followed by an equality operator and then the column name.
Personally, I like to do it this way:
Select [BookShopDB].[dbo].[tblCustomers].[Customer ID] AS 'The new Customer', ...
The third way is:
Select [BookShopDB].[dbo].[tblCustomers].[Customer ID] 'The new Customer', ...
The word 'AS' is removed. | |
| cocolocopolo 2002-07-03, 12:12 am |
| Dear kingsunl, thanks for your advice.
The MCSE Training Kit likes us to come up a result in this way:
"The new customer is 11 (or 12 so on so on)'
So I follow your suggesttion and changed the last "Select" clause like this way:
Select [BookShopDB].[dbo].[tblCustomers].[Customer ID] AS 'The new Customer'
but after this clause, how can I combine the @r_Code so the result can come up a customer ID number???
Thanks in advance. | |
| kingsunl 2002-07-03, 9:58 am |
| quote:
But I got error:
Server: Msg 208, Level 16, State 1, Procedure AddCustomer, Line 16
Invalid object name 'BookShopDB.dbo.Customers'.
You must look at line 16 of AddCustomer procedure. There is no point modifying the current codes that call this procedure.
quote:
Else If @r_Code = @@Identity
Select [Customer ID] = 'The new customer is: ' +
Convert(Char(2), @r_Code)
will work if AddCustomer procedure is straightened out. |
|
|
|
|