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.

  • SQL Syntax to Pivot multiple tables
  • At what cardinality does SQL Server switch to an index scan (vs. seek)
  • SQL Server equivalent to MySQL enum data type?
  • INSERT / UPDATE data using DBNull or null?
  • Creating a table that will give me a list of books with and without reviews for the ones that do
  • Complex SQL query for inventory app
  • 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;
    

  • ADO.net SqlTransaction improves performance
  • SSIS ETL Transform-Load How To Handle Create / Update (i.e. UPSERT) for Foreign Key Table Data?
  • How to cast datetime to datetimeoffset?
  • Obtaining Sums from Multiple Tables
  • SSRS BIDS 2008 Reporting Drop Down menu
  • How to handle ascii control characters in sql server?
  • 3 Solutions collect form web for “How do I add errror handling into this stored procedure?”

    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;
    

    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
    

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.