How to use If Else if and Else in stored procedures | SQL Server

Question

I have created the following stored procedure where user passes input parameters to get the data. The input parameters are optional i.e. passing just one parameter should produce some data.

Here is my code.

ALTER PROC [dbo].[SPR_ECRM_CASE_INFORMATION] 

@ID_APPLICATION_NO NVARCHAR(20),
@TX_ID_NO NVARCHAR(20)

AS 
    BEGIN
        SET NOCOUNT ON

        IF (@ID_APPLICATION_NO IS NOT NULL ) OR (LEN(@ID_APPLICATION_NO) > 0) AND ((@TX_ID_NO IS NOT NULL) OR (LEN(@TX_ID_NO) > 0)) 
            BEGIN
                SELECT DISTINCT
                 [A].[ID_APPLICATION_NO]
                ,[A].[TX_APPLICATION_STATUS]
     
                FROM [VW_T_APPLICATION] As [A]
                INNER JOIN [CT_L1_APPLICATION_STATUS] [L] ON [L].[TX_L1_APPLICATION_STATUS_CODE] = [A].[TX_APPLICATION_STATUS_CODE]
                LEFT JOIN [VW_ST_APPLICATION_APPLICANT] [I] WITH(NOLOCK) ON [I].[ID_APPLICATION_APPLICANT] = (SELECT TOP 1   
                [II].[ID_APPLICATION_APPLICANT]
                FROM  
                    [ST_APPLICATION_APPLICANT] [II] WITH(NOLOCK)
                WHERE [II].[ID_APPLICATION_GUID] = [A].[ID_APPLICATION_GUID]
                    AND [II].[CD_APPLICANT_TYPE] = 81
                    AND [II].[IN_ACTIVE] = 1
                ORDER BY 
                    [II].[ID_APPLICATION_APPLICANT] DESC)
                LEFT JOIN [VW_ST_APPLICATION_APPLICANT] [J] WITH(NOLOCK) ON [J].[ID_APPLICATION_APPLICANT] = (SELECT TOP 1   
                    [JJ].[ID_APPLICATION_APPLICANT]
                FROM  
                    [ST_APPLICATION_APPLICANT] [JJ] WITH(NOLOCK)
                WHERE [JJ].[ID_APPLICATION_GUID] = [A].[ID_APPLICATION_GUID]
                    AND [JJ].[CD_APPLICANT_TYPE] = 82
                    AND [JJ].[IN_ACTIVE] = 1
                ORDER BY 
                    [JJ].[ID_APPLICATION_APPLICANT] DESC)
 
                WHERE
                [I].[TX_ID_NO] = @TX_ID_NO AND [J].[TX_ID_NO] = @TX_ID_NO
                AND
                [A].[TX_APPLICANT_ID_NO] = @ID_APPLICATION_NO
                AND
                [A].[IN_ACTIVE] = 1

            END

         ELSE IF (@ID_APPLICATION_NO IS NOT NULL) OR (LEN(@ID_APPLICATION_NO) > 0)
            BEGIN
                SELECT DISTINCT
                 [A].[ID_APPLICATION_NO]
                ,[A].[TX_APPLICATION_STATUS]
     
                FROM [VW_T_APPLICATION] As [A]
                INNER JOIN [CT_L1_APPLICATION_STATUS] [L] ON [L].[TX_L1_APPLICATION_STATUS_CODE] = [A].[TX_APPLICATION_STATUS_CODE]
                LEFT JOIN [VW_ST_APPLICATION_APPLICANT] [I] WITH(NOLOCK) ON [I].[ID_APPLICATION_APPLICANT] = (SELECT TOP 1   
                        [II].[ID_APPLICATION_APPLICANT]
                    FROM  
                        [ST_APPLICATION_APPLICANT] [II] WITH(NOLOCK)
                    WHERE [II].[ID_APPLICATION_GUID] = [A].[ID_APPLICATION_GUID]
                        AND [II].[CD_APPLICANT_TYPE] = 81
                        AND [II].[IN_ACTIVE] = 1
                    ORDER BY 
                        [II].[ID_APPLICATION_APPLICANT] DESC)
                LEFT JOIN [VW_ST_APPLICATION_APPLICANT] [J] WITH(NOLOCK) ON [J].[ID_APPLICATION_APPLICANT] = (SELECT TOP 1   
                        [JJ].[ID_APPLICATION_APPLICANT]
                    FROM  
                        [ST_APPLICATION_APPLICANT] [JJ] WITH(NOLOCK)
                    WHERE [JJ].[ID_APPLICATION_GUID] = [A].[ID_APPLICATION_GUID]
                        AND [JJ].[CD_APPLICANT_TYPE] = 82
                        AND [JJ].[IN_ACTIVE] = 1
                    ORDER BY 
                        [JJ].[ID_APPLICATION_APPLICANT] DESC)
 
                WHERE
                [A].[TX_APPLICANT_ID_NO] = @ID_APPLICATION_NO
                AND 
                [A].[IN_ACTIVE] = 1
                

            END

          ELSE IF (@TX_ID_NO IS NOT NULL) OR (LEN(@TX_ID_NO) > 0)
            BEGIN
                SELECT DISTINCT
                 [A].[ID_APPLICATION_NO]
                ,[A].[TX_APPLICATION_STATUS]
     
                FROM [VW_T_APPLICATION] As [A]
                INNER JOIN [CT_L1_APPLICATION_STATUS] [L] ON [L].[TX_L1_APPLICATION_STATUS_CODE] = [A].[TX_APPLICATION_STATUS_CODE]
                LEFT JOIN [VW_ST_APPLICATION_APPLICANT] [I] WITH(NOLOCK) ON [I].[ID_APPLICATION_APPLICANT] = (SELECT TOP 1   
                    [II].[ID_APPLICATION_APPLICANT]
                FROM  
                    [ST_APPLICATION_APPLICANT] [II] WITH(NOLOCK)
                WHERE [II].[ID_APPLICATION_GUID] = [A].[ID_APPLICATION_GUID]
                    AND [II].[CD_APPLICANT_TYPE] = 81
                    AND [II].[IN_ACTIVE] = 1
                ORDER BY 
                    [II].[ID_APPLICATION_APPLICANT] DESC)
                LEFT JOIN [VW_ST_APPLICATION_APPLICANT] [J] WITH(NOLOCK) ON [J].[ID_APPLICATION_APPLICANT] = (SELECT TOP 1   
                    [JJ].[ID_APPLICATION_APPLICANT]
                FROM  
                    [ST_APPLICATION_APPLICANT] [JJ] WITH(NOLOCK)
                WHERE [JJ].[ID_APPLICATION_GUID] = [A].[ID_APPLICATION_GUID]
                    AND [JJ].[CD_APPLICANT_TYPE] = 82
                    AND [JJ].[IN_ACTIVE] = 1
                ORDER BY 
                    [JJ].[ID_APPLICATION_APPLICANT] DESC)
 
                WHERE
                [I].[TX_ID_NO] = @TX_ID_NO OR [J].[TX_ID_NO] = @TX_ID_NO
                AND
                [A].[IN_ACTIVE] = 1

            END
        ELSE
        BEGIN
            RETURN NULL
        END
        END

I have used if else if and else condition but it fails to return the data.

DECLARE @A NVARCHAR(30)
DECLARE @B NVARCHAR(20)

SET @A = 'C192'
SET @B = 'ABC' 

EXEC SPR_ECRM_CASE_INFORMATION @A, ''
EXEC SPR_ECRM_CASE_INFORMATION '', @B
EXEC SPR_ECRM_CASE_INFORMATION '', ''

Firstly, the else if condition fails and my code here is redundant. Is there any better way to optimize this. Your help is appreciated.

Randy in Marin · Accepted Answer · 2020-11-11 01:34:20Z

If you want to optimize for performance, you can create a single procedure for each query. Each will get it's own query plan. You can call each in this procedure.

Looking at each query, I see the sub-query in the ON clause. I also see NOLOCK which I normally consider a bad sign. Also see the DISTINCT. These are all clues that the query can be simplified. SQL Server will then be more likely to optimize the query. (You can ask for the same thing in more than one way. The more complex the logic, the less likely SQL will find the best plan.)

If the data in [VW_T_APPLICATION] is already distinct, then get rid of DISTINCT and use EXISTS instead of LEFT JOINS - one EXISTS for 81 and one for 82. (I'm assuming you are not going to need columns from these.)

The use of "VW" implies the use of views. The view can hide a lot of logic that gets in the way of a simple query.

seanb · Accepted Answer · 2020-11-10 23:42:34Z

0

Is this part (your first IF) correct in terms of bracketing? It will run if @ID_Application_No is NOT NULL, regardless of whether @TX_ID_No has a value.

IF (@ID_APPLICATION_NO IS NOT NULL ) OR (LEN(@ID_APPLICATION_NO) > 0) AND ((@TX_ID_NO IS NOT NULL) OR (LEN(@TX_ID_NO) > 0))

I'm guessing you want either

a) brackets around the ID_Application_No part e.g.,

IF ((@ID_APPLICATION_NO IS NOT NULL ) OR (LEN(@ID_APPLICATION_NO) > 0)) AND ((@TX_ID_NO IS NOT NULL) OR (LEN(@TX_ID_NO) > 0))

or b) want it to be an AND (this is my most likely guess) e.g.,

IF (@ID_APPLICATION_NO IS NOT NULL ) AND (LEN(@ID_APPLICATION_NO) > 0) AND ((@TX_ID_NO IS NOT NULL) OR (LEN(@TX_ID_NO) > 0))

Note you can simplify some things e.g., when you have IF (@ID_APPLICATION_NO IS NOT NULL) OR (LEN(@ID_APPLICATION_NO) > 0), the first part will always be true if the second part is true. In those cases, you can use only IF (@ID_APPLICATION_NO IS NOT NULL) and it will give the same results.

edited Nov 10, 2020 at 23:42

answered Nov 10, 2020 at 23:37

seanb

6,6952 gold badges7 silver badges24 bronze badges

3 Comments

Randy in Marin Over a year ago

I think you meant to use the second part. LEN(@ID_APPLICATION_NO) > 0 will be true if both non-null and non-blank.

seanb Over a year ago

@Randy - if it was AND between them, then yes you'd only use the LEN() version - but given it's an OR, the IS NOT NULL version is appropriate I believe.

Randy in Marin Over a year ago

@seanb, I mention this just in case the requirement is non-blank rather than not null.

Collectives™ on Stack Overflow

How to use If Else if and Else in stored procedures | SQL Server

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related