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

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';

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.
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.
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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related