I need to print a list of countries together with other number of customers etc when a user enters a country name. My if loop works but the else loop doesn't work. The else loop should be executed when a user enters a country that is not in the column. It should print the message and the list of countries to choose from. Can someone say what is wrong in my code?

create or alter proc [usp_sales per country total plus different customer]
    @land varchar(100)
as
    if exists (select shipcountry from Sales.Orders)
    begin
        select distinct 
            shipcountry, 
            count(orderid) as CustomerTotal,
            count(distinct custid) as [Different Customer]
        from 
            sales.orders
        where 
            @land = shipcountry
        group by 
            shipcountry
    end
    else if not exists (select shipcountry from Sales.Orders)
    begin
        print 'Land nicht gefunden - Wählen Sie eines aus der Liste aus'

        select shipcountry 
        from Sales.orders
    end


exec [usp_sales per country total plus different customer] china

CodePudding user response：

This line of code in the if will always return something because you have no where clause

select shipcountry from Sales.Orders

Try something like this

select 1 from Sales.Orders where shipcountry = @land

and remove the condition on the else, not vary maintianable to have the same condition reversed in both places.