asked
If-then-Else in Stored Procedure
Hi All, I am trying to create a IF-THEN-ELSE SProc as below. Fairly new to SProc, so just wanted to confirm that if I run this SProc from SSRS and just select `@SCANumber` as `NUll` from the dropdown (on SSRS Frontend) then will it only run the top portion of the query and accordingly when I select `@FSANumber null` from my dropdown, only the bottom portion of the query should run. CREATE PROCEDURE [dbo].[rpt_WithoutExternalReferences] ( @SCANumber INT , @FSANumber INT ) AS BEGIN SET NOCOUNT ON IF ( @SCANumber = NULL ) BEGIN SELECT adviser_account.adviser_account_id , dealer.old_system_reference , external_reference.external_reference AS Missing_refernce , entity.name AS Name , entity.given_names AS Given_Names FROM [uvw_adviser_account] AS adviser_account INNER JOIN [uvw_entity] AS entity ON adviser_account.entity_id = entity.entity_id INNER JOIN [uvw_dealer_branch] AS dealer ON adviser_account.dealer_branch_id = dealer.dealer_branch_id LEFT JOIN [uvw_party_external_reference] AS external_reference ON adviser_account.adviser_account_id = external_reference.party_id WHERE external_reference.party_type_id = 3 END ELSE IF ( @FSANumber = NULL ) BEGIN SELECT dealer.dealer_id , dealer.old_system_reference , dealer.fsa_firm_reference_id AS Missing_refernce , entity.name AS Name , entity.given_names AS Given_Names FROM [uvw_dealer] AS dealer INNER JOIN [uvw_entity] AS entity ON dealer.entity_id = entity.entity_id WHERE dealer.fsa_firm_reference_id IS NULL END END
You should be using @SCANumber IS NULL. Also make sure the alias all the fields to the same names otherwise reporting services might not pick it up (advisor_account_id and dealer_id should have the same labels). The second part will only execute if @SCANumber IS **NOT** NULL **AND** @FSANumber IS NULL. Not sure if this is your desired behaviour.
15 People are following this question.
