Changing stored procedure

Question

I have a proc that print checks if there is any new checks to be print. If there is nothing to issue new checks it wont print any. Now i want to modify this proc like even if i don't have any new checks to be print, it should pick up at least one check to be print.( even if it is already printed). Can you tell me how to do that. Here is the stored proc.

CREATE PROCEDURE [proc_1250_SELCashiersChecksForPrint] AS

SELECT t_DATA_CashiersChecksIssued.ControlNbr, 
    t_DATA_CashiersChecksIssued.Audit_DateAdded, 
    t_DATA_CashiersChecksIssued.BatchNbr, 
    t_DATA_CashiersChecksIssued.SerialNbr, 
    t_DATA_CashiersChecksIssued.CheckRTN, 
    t_DATA_CashiersChecksIssued.CheckAccountNbr, 
    t_DATA_CashiersChecksIssued.Amount, 
    t_DATA_CashiersChecksIssued.DateIssued, 
    t_DATA_CashiersChecksIssued.Payee, 
    t_DATA_CashiersChecksIssued.Address, 
    t_DATA_CashiersChecksIssued.City, 
    t_DATA_CashiersChecksIssued.State, 
    t_DATA_CashiersChecksIssued.Zip, 
    t_DATA_Reclamation.ClaimId, 
    t_DATA_Reclamation.NoticeDate, 
    t_DATA_Reclamation.FirstName, 
    t_DATA_Reclamation.MiddleName, 
    t_DATA_Reclamation.LastName, 
    t_DATA_Reclamation.ClaimTotal, 
    t_PCD_Claimant.Name AS Agency, 
    t_DATA_CashiersChecksIssued.IDENTITYCOL
FROM t_DATA_CashiersChecksIssued INNER JOIN
    t_DATA_Reclamation ON 
    t_DATA_CashiersChecksIssued.ControlNbr = t_DATA_Reclamation.ControlNbr
     INNER JOIN
    t_PCD_Claimant ON 
    t_DATA_Reclamation.ClaimantCode = t_PCD_Claimant.ClaimantCode
WHERE (t_DATA_CashiersChecksIssued.SerialNbr IS NULL) AND 
    (t_DATA_CashiersChecksIssued.DateIssued IS NULL)
ORDER BY t_DATA_CashiersChecksIssued.Audit_DateAdded ASC, 
         t_DATA_CashiersChecksIssued.ControlNbr ASC

Let me know if you need more information.

Answer 1

Use the TOP n SQL syntax :

if EXISTS ( /* Look for an unprinted check - "date_issued is null" */ )
   /* print unprinted checks */
ELSE
   select top 1 /* already-printed-checks  */
   where .... "date_issued is not null"

OR

Do you want to print a "Voided/Cancelled" check - when you do that?

Answer 2

You have to decide how you want to pick your "at least one".

The simplest way (probably) is to remove whatever condition in the WHERE clause is excluding already printed checks. Let's assume that's t_DATA_CashiersChecksIssued.DateIssued IS NULL. Now add a column to your SELECT clause like this: CASE WHEN t_DATA_CashiersChecksIssued.DateIssued IS NULL then 0 ELSE 1 END and make that column first in your ORDER BY clause.

Now in the procedure, fetch just one row from this cursor. If this new column has the value 0, there's at least one new check to be processed and you should iterate through the cursor but stop when you get to an already issued one. If it has the value 1, there are no new checks.

Edit: The other approach would be to do it right in your SQL. Leave the original as is, but add a clause like: UNION ALL SELECT ... AND ROWNUM = 1 where ... represents your existing query, but with the condition to exclude already printed checks removed. On second thought, this may be simpler.

Answer 3

SELECT TOP 1 t_DATA_CashiersChecksIssued.ControlNbr, t_DATA_CashiersChecksIssued.Audit_DateAdded, t_DATA_CashiersChecksIssued.BatchNbr, t_DATA_CashiersChecksIssued.SerialNbr, t_DATA_CashiersChecksIssued.CheckRTN, t_DATA_CashiersChecksIssued.CheckAccountNbr, t_DATA_CashiersChecksIssued.Amount, t_DATA_CashiersChecksIssued.DateIssued, t_DATA_CashiersChecksIssued.Payee, t_DATA_CashiersChecksIssued.Address, t_DATA_CashiersChecksIssued.City, t_DATA_CashiersChecksIssued.State, t_DATA_CashiersChecksIssued.Zip, t_DATA_Reclamation.ClaimId, t_DATA_Reclamation.NoticeDate, t_DATA_Reclamation.FirstName, t_DATA_Reclamation.MiddleName, t_DATA_Reclamation.LastName, t_DATA_Reclamation.ClaimTotal, t_PCD_Claimant.Name AS Agency, t_DATA_CashiersChecksIssued.IDENTITYCOL FROM t_DATA_CashiersChecksIssued INNER JOIN t_DATA_Reclamation ON t_DATA_CashiersChecksIssued.ControlNbr = t_DATA_Reclamation.ControlNbr INNER JOIN t_PCD_Claimant ON t_DATA_Reclamation.ClaimantCode = t_PCD_Claimant.ClaimantCode ORDER BY t_DATA_CashiersChecksIssued.Audit_DateAdded DESC