How do I add an optional parameter to a T_SQL stored procedure?

Question

Let's say I have have the following parameters:

@firstName varchar(50), @middleName varchar(50), @LastName varchar(50)

I want the middle name to be optional, How do I return the value (@portalUserName) without middle name if the middle name is not passed as an input parameter?

PS: I need the middle names first character to be added in the user name if the user has a middle name (i.e. if middle name value isn't null)

CREATE PROCEDURE [dbo].[cst_sproc_UserName_Get]    
    (@firstName VARCHAR(50), 
     @middleName VARCHAR(50),
     @LastName VARCHAR(50)   
    )    
AS    
BEGIN   
    DECLARE @portalUserName VARCHAR(50)  

    SET @portalUserName = SUBSTRING(UPPER(RTRIM(@firstname)), 1, 1) + 
                          SUBSTRING(UPPER(RTRIM(@firstname)), 1, 1) + 
                          LOWER(RTRIM(@LastName))  

    IF NOT EXISTS (SELECT 'TRUE' FROM wpUser WHERE UserCode = @portalUserName)  
    BEGIN  
        SELECT @portalUserName UserCode  
        RETURN
    END 
END

Can you clarify where the Middle name needs to be added to the username? Does it need to be at the end or the middle or the front of the username? — Khal_Drogo
– Khal_Drogo, Commented Feb 25, 2019 at 21:35
I think you may be working under an illusion. "Optional" in this context means it is optional for the caller of the procedure - the procedure itself always receives something for each parameter. The procedure logic cannot know if the caller provided a value for any given parameter. You would need to use a special "flag" value (e.g., null, empty string) to let the procedure "know" that it was not provided (or should be ignored etc.). — SMor
– SMor, Commented Feb 25, 2019 at 23:47

Luis Cazares · Accepted Answer · 2019-02-25 20:59:30Z

2

To make a parameter optional, you need to give it a default value, which could be NULL.

CREATE  PROC [dbo].[cst_sproc_UserName_Get]    
    (    
     @firstName varchar(50), 
     @middleName varchar(50) = NULL,
     @LastName varchar(50)   

    )    
    AS    
    BEGIN   

    Declare @portalUserName varchar(50)  
    SET @portalUserName = SUBSTRING(upper(RTRIM(@firstname)),1,1)+ SUBSTRING(upper(RTRIM(@firstname)),1,1) + lower(RTRIM(@LastName))  

    IF NOT EXISTS (SELECT 'TRUE' FROM wpUser WHERE UserCode = @portalUserName)  
    BEGIN  

     SELECT @portalUserName UserCode  
     Return  

    END

But to call it, you need to follow Best Practices and call your procedure with parameter naming.

EXEC [dbo].[cst_sproc_UserName_Get] @firstName = 'Luis', @LastName = 'Cazares';

answered Feb 25, 2019 at 20:59

Luis Cazares

3,60510 silver badges24 bronze badges

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

3 Comments

JMabee Over a year ago

If you set @@middlename to use NULL as the default your procedure will return a NULL string. Inserting a NULL value into the middle of a string returns NULL. Given what you are doing I would declare \@middlename varchar(50) = '' for the default value.

Luis Cazares Over a year ago

Actually, it wouldn't matter in this case because @middlename is not used at all. But handling NULLs is something that should be done always, as you could send a NULL into a mandatory parameter.

JMabee Over a year ago

I would imagine that the OP just typed in the query wrong and typed in the firstname variable twice. I say that given his description of the problem. Had that been the case, and given how he is using those parameters putting a NULL in as the default would return a NULL string.

Collectives™ on Stack Overflow

How do I add an optional parameter to a T_SQL stored procedure?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related