Check if a parameter is null or empty in a stored procedure

Apply defaults using COALESCE

In instances where your stored procedure ought to proceed with a default value instead of stopping due to blank input, COALESCE comes to your rescue:

-- Turn nothing to something with COALESCE and NULLIF
SET @Param = COALESCE(NULLIF(@Param, ''), 'Default Value') -- Magic happens here


This sets @Param to 'Default Value' if it's null or empty, exploiting NULLIF which returns NULL when @Param is an empty string and COALESCE to replace it ultimately with a default value.

Establish consistency

Don't play with fire, maintain consistency in your SQL code. Stick to NVARCHAR(50) or any other definite type consistently across your procedures to combat unforeseen type conversion issues.

Checks within control structures

Embed parameter checks as part of flow control mechanisms:

-- Working smarter not harder with COALESCE and NULLIF
IF COALESCE(NULLIF(@Param, ''), 'Default Value') = 'Default Value'
BEGIN
    PRINT 'Rolling with default value due to NULL or empty input.'
END


In this configuration, the control structures in your stored procedure are adaptive, modifying themselves based on the content of the parameter. This ramps up your logic's flexibility.

Gear towards real scenarios

Suppose you foresee the influx of dates, apply the same principle:

DECLARE @StartDate NVARCHAR(50);
-- Y2K is no longer an issue, 2000 is safe!
SET @StartDate = COALESCE(NULLIF(@StartDate, ''), '01/01/2000') -- Set default date if null or empty


Guaranteeing a default start date bypasses disruption, aiding continuous operation.

Refining code for readability

Remember code is read more often than written, refactor for readability. Here is a revamped check, with explicit length comparison for clarity:

-- Not your usual spot the difference contest
IF @Param IS NULL OR LEN(@Param) = 0


Leverage undocumented gems

Beyond the textbook practices, real SQL hacks and tricks yield tons of efficiency. For instance, ISNULL offers a swift default when initializing a variable:

-- Because who doesn't like today's date?
DECLARE @EndDate NVARCHAR(50) = ISNULL(@EndDateInput, GETDATE())


Just like that, you'll have today's date as the end date if none is provided.