CODEWITHSUNDEEP
sql-serverstored-procedures
Thando Tee
Thando Tee

Reputation: 121

IF statement in SQL Server stored procedure

I have the following stored procedure:

ALTER PROCEDURE [dbo].[sp_Detail]   
      @ReceiptNumber int
AS
BEGIN
    SET NOCOUNT ON;   

    WITH invoiceT AS 
    (SELECT 
       fs_transaksie.enti AS Entity,
       fs_transaksie.rek AS Account,
       fs_transaksie.trans_tipe AS TransactionType,
       fs_transaksie.verwysnr AS ReferenceNumber    
    FROM mf_history.dbo.fs_transaksie      
    WHERE ( fs_transaksie.verwysnr = @ReceiptNumber ) AND (fs_transaksie.trans_tipe = 3))
 , transactionT as
    (SELECT  
       fs_kwitansie.kwitansienr AS InvoiceNumber,
       fs_kwitansie.ktkaart_nr AS CreditCardNumber,
       fs_kwitansie.ktkaart_bank AS CCBank,
       fs_kwitansie.ktkaart_bedrag AS CCAmount    
    FROM mf_history.dbo.fs_kwitansie     
    WHERE ( fs_kwitansie.kwitansienr = @ReceiptNumber ) 
)
select * 
from invoiceT 
full outer join transactionT on invoiceT.ReferenceNumber = transactionT.InvoiceNumber  
END

If the fs_transaksie.trans_tipe field = 3 and fs_transaksie.rek = 5205 then an error message needs to be displayed to the user. He may NOT view the data

Upvotes: 0

Views: 73

Answers (1)

Simon1979
Simon1979

Reputation: 2108

If you can just not display the data, rather than raising an error then changing your WHERE clause to:

WHERE ( fs_transaksie.verwysnr = @ReceiptNumber ) AND (fs_transaksie.trans_tipe = 3)
    And (fs_transaksie.rek != 5205)

should exclude it - you are already filtering it to just be trans_tipe = 3 so adding the And (fs_transaksie.rek != 5205) will exclude any where the results are 3 and 5205 respectively.

Upvotes: 1

Related Questions