Writing a Stored Procedure If/Else to create error message

Question

I am trying to create a stored procedure with an if/else statement that will result in a Text message if the wrong CustomerID is entered into the stored procedure. As it is now it will only give me a print line when there is nothing entered in the CustomeID.

Create proc spCustOrder
@CustomerID VarChar(10),
@StartDate SmallDateTime = null,
@EndDate SmallDateTime =  NUll
as 
Begin
iF @CustomerID > 0

Select Distinct OrderID, OrderDate
from Customer C  join CustOrder CO on CO.CustomerID = C.CustomerID
where C.CustomerID  = @CustomerID and 
OrderDate >= Isnull(@startDate,'1900-01-01') and 
OrderDate <= IsNull(@EndDate, getDate( ))
Else 
Print 'Please enter a CustomerID'
end

Basically what i am unclear on is what should i use instead of the "0" in this "@CustomerID > 0" to make the program function. i tried using CustomerID or C. and CO.CustomerID but it says that there is an error with using that command.

Answer 1

Try

IF Exists(
    SELECT DISTINCT OrderID, ...
    )
ELSE
    PRINT ...
END

Also, you typically want to return an ID or true/false value from stored procedures and do any printing or IO in the routine that calls the proc; avoid doing IO in the proc itself.

Answer 2

Your query as written doesn't seem to require the Customers table, so the query can be written as:

Select OrderID, OrderDate
from  CustOrder CO
where CO.CustomerID  = @CustomerID and 
      OrderDate >= Isnull(@startDate,'1900-01-01') and 
      OrderDate <= IsNull(@EndDate, getDate( ));

You then want to print something when there are no rows in the table. I would suggest using a temporary table for storing the intermediate results so they don't have to be calculated twice. The result is something like this:

Create procedure spCustOrder (
    @CustomerID VarChar(10),
    @StartDate SmallDateTime = null,
    @EndDate SmallDateTime =  NUll
)
as Begin

    Select OrderID, OrderDate
    into #tmp
    from  CustOrder CO
    where CO.CustomerID  = @CustomerID and 
          OrderDate >= Isnull(@startDate,'1900-01-01') and 
          OrderDate <= IsNull(@EndDate, getDate( ));

    if exists (select 1 from #tmp)
    begin
        select *
        from #tmp;
    end
    else 
        Print 'Please enter a CustomerID'
end;  -- spCustOrder