CODEWITHSUNDEEP
sqlsql-servercursorprocedure
Just_Some_Guy
Just_Some_Guy

Reputation: 330

how to write a SQL procedure to print out message if no results are found

I'm working on writing a procedure to display all customers in a given state. The user would enter in the two character abbreviation (exp: CA, PA) and it will display all customers in the given state. The catch is I need to print out a message if there are no customers in the given state (like 'GA'). What I have below is working, but I feel like there might be an easier / more efficient way to do it. Any feed back is welcome; one area I'm confused about is how to clean up my message I'm printing out. also, please note this is for a school assignment.

this is my procedure thus far:

    create procedure display_customer_state
(@state char (2))
as 
begin

--begin cursor
declare customer_state_check cursor for
select address_state
from customer
for read only

declare @entered_cust_state as char(2), @state_count as int 
set @state_count = 0
open customer_state_check
fetch customer_state_check into @entered_cust_state

    while @@FETCH_STATUS = 0
    begin

        if @entered_cust_state = @state
        begin
        set @state_count = @state_count+1
        end

fetch customer_state_check into @entered_cust_state

end
close customer_state_check
deallocate customer_state_check
--cursor end

if @state_count = 0
begin
print 'sorry, there are 0 customers in '
print @state
end

select first_name,last_name, primary_address, address_zip_code, address_state
from customer
where address_state = lower(@state)

end

Upvotes: 0

Views: 128

Answers (1)

StuartLC
StuartLC

Reputation: 107237

Ouch - you certainly don't need a cursor to count the number of records matching a filter in a table. A filtered count should be able to replace the whole cursor:

declare @state_count as int = 0;

select @state_count = COUNT(numaddress_state)
from customer
where customer_state_check = @state;

Upvotes: 1

Related Questions