CODEWITHSUNDEEP
sql-servert-sql
allendks45
allendks45

Reputation: 341

How do I add errror handling into this stored procedure?

How can I print an error message from this procedure if the employee (server) hasn't served anyone? Are try - catch blocks the only way to handle this?

I was thinking that if/else condition test followed by Print message suits my requirement.

Stored procedure:

if OBJECT_ID('customers_served', 'u') is not null
    drop procedure customers_served;
go

create procedure customers_served
    @employee_id int
as 
    set nocount on;

    select 
        c.customer_id, c.cust_lastname, 
        (sum(c.cust_total_guests)) as total_guests
    from 
        customers c
    join 
        seating s on c.customer_id = s.customer_id
    join 
        table_assignment ta on s.table_id = ta.table_id
    join 
        employees e on ta.employee_id = e.employee_id
    where 
        @employee_id = e.employee_id
    group by 
        c.customer_id, c.cust_lastname;

/* if total_guests = 0 print message the employee has served 0 guests */

Test procedure:

exec customers_served
        @employee_id = 5;

Upvotes: 1

Views: 60

Answers (3)

Brandon
Brandon

Reputation: 702

Rather than double querying, you can simply test @@ROWCOUNT after your query to determine if any results were returned, and print your message if @@ROWCOUNT = 0.

Upvotes: 0

Jason
Jason

Reputation: 945

I modified your script to this.

use dbServers;

if OBJECT_ID('customers_served', 'u') is not null
drop procedure customers_served;
go

create procedure customers_served
@employee_id    int
as 
set nocount on;

declare @totalGuests int;

set @totalGuests = (
 select(sum(c.cust_total_guests))
 from customers c
 join seating s on c.customer_id = s.customer_id
 join table_assignment ta on s.table_id = ta.table_id
 join employees e on ta.employee_id = e.employee_id
 where @employee_id = e.employee_id
)

if @totalGuests = 0 OR @totalGuests IS NULL
BEGIN
 print 'This server did not serve any guests'
END
else
BEGIN
 select @totalGuests AS 'total_quests'
END

/* test procedure*/
exec customers_served
@employee_id = 5;

Upvotes: 1

Biswabid
Biswabid

Reputation: 1409

Following snippet of code might help:

declare @r int

select @r = (sum(c.cust_total_guests)) as  total_guests
from customers c
join seating s on c.customer_id = s.customer_id
join table_assignment ta on s.table_id = ta.table_id
join employees e on ta.employee_id = e.employee_id
where @employee_id = e.employee_id
group by c.customer_id, c.cust_lastname;

if @r = 0
begin
-- do what ever you wish
end
else
begin
select c.customer_id, c.cust_lastname, (sum(c.cust_total_guests)) as      
       total_guests
from customers c
join seating s on c.customer_id = s.customer_id
join table_assignment ta on s.table_id = ta.table_id
join employees e on ta.employee_id = e.employee_id
where @employee_id = e.employee_id
group by c.customer_id, c.cust_lastname;
end 
end

Upvotes: 1

Related Questions