Creating an Error Message using If/Else Statements in a Stored Procedure

When it comes to handling errors in procs, I follow the same approach as I do in applications. If there is scope for adding value by handling the error, then it should be done, otherwise, it’s best to leave it. For instance, in some procs, I include contextual information in the error message, such as a list of conflicting ID values during an update operation.

Question:

My objective is to formulate a stored procedure that comprises an if/else statement, with an aim to produce a Text message in the event of an incorrect CustomerID input. The current implementation only generates a print line if no CustomerID is entered.

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

I’m not certain what to replace the “0” with in the “@CustomerID > 0” line to make the program work. I attempted to use CustomerID, C, and CO.CustomerID, but it resulted in an error.


Solution 1:

Try

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

It’s advisable to have
stored procedures
return an ID or true/false value, and refrain from any printing or IO within the proc. Instead, perform any printing or IO in the routine that calls the proc.


Solution 2:


The query you’ve written doesn’t appear to need the

Customers

table, therefore the query can be written in a different way.

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

To avoid repeating calculations, it is recommended to utilize a
temporary table
for storing intermediate results. This way, when there are no rows in the table, you can print the stored values without having to recalculate them. The resulting code would look 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


Solution 3:


To ensure that a CustomerID is provided by the user, I have implemented the

RAISERROR

function in SQL Server to generate an error message if it is missing.

The

RETURN

stops the code execution and exits, preventing any further lines of code from executing after encountering the

RETURN

keyword. For instance, if the @CustomerID value is null or 0 and the control enters the

IF Block

, the code execution will stop.

Create proc spCustOrder
@CustomerID VarChar(10) = NULL,
@StartDate SmallDateTime = null,
@EndDate SmallDateTime =  NUll
AS
BEGIN
  SET NOCOUNT ON;
  IF (@CustomerID IS NULL OR @CustomerID = 0)
   BEGIN
    RAISERROR('Please enter a CustomerID',16,1)
    RETURN;
   END
    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( ))
END


Solution 4:

Honestly, my approach would differ significantly. One can utilize code such as:

ALTER PROCEDURE name
@CustomerID (uniqueidentifier) - varchar is very bad solution for your performance
@date1 DATE,
@date2 DATE,
@result INT OUTPUT=0 - use it as output parameter and handle text in app code
AS
BEGIN TRAN
IF EXISTS (your SELECT query. You don't need DISTINCT)
BEGIN
-- if exists do something smart. I don't know... UPDATE TABLE
IF @@error<>0
BEGIN
SET @result=1 --there was an error executing query in something smart
END
ELSE - from IF EXISTS statement
BEGIN
SET @result=2 --means no records found
END
IF @result=1
BEGIN
RETURN 1 --you know that this is error from your application code
ROLLBACK TRAN --there was an error in doing something smart
END
IF @result=2
RETURN 2 -- from your application code you know that this means no users found
END
IF RETURN=0
BEGIN
RETURN 0 -- userid found and something smart done without error :)
COMMIT TRAN
END 

Consider analyzing in more detail the actions you plan to take on the application level and those on the DB level. If this suggestion has been helpful, please acknowledge it. But if you still have any doubts or queries, feel free to ask.

Frequently Asked Questions

Posted in Sql