IF statement in SQL Server stored procedure

Question

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

Does it need to be an error message or can you just not display the data? — Simon1979
– Simon1979, Commented May 12, 2014 at 7:03
@Thando Tee - In what field do you want to display the Error? — Yakov R.
– Yakov R., Commented May 12, 2014 at 7:05
You can write a WHERE clause 'TransactionType!=3 OR Account!=5205' — Yakov R.
– Yakov R., Commented May 12, 2014 at 7:07
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented May 12, 2014 at 7:11

Simon1979 · Accepted Answer · 2014-05-12 07:09:33Z

1

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.

answered May 12, 2014 at 7:09

Simon1979

2,1081 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

IF statement in SQL Server stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related