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

Question

I know how to check if a parameter is null but i am not sure how to check if its empty ... I have these parameters and I want to check the previous parameters are empty or null and then set them like below

ALTER PROCEDURE [dbo].[GetSummary]
    @PreviousStartDate NVARCHAR(50) ,
    @PreviousEndDate NVARCHAR(50) ,
    @CurrentStartDate NVARCHAR(50) ,
    @CurrentEndDate NVARCHAR(50)
AS
  BEGIN
    IF(@PreviousStartDate IS NULL OR EMPTY)
        SET @PreviousStartdate = '01/01/2010'  for example..

I would appreciate the help.

Do you care if `@PreviousStartDate` is not just `NULL` or `''` but contains whitespace, e.g. `' '`. — Jodrell, Sep 20 '22 at 07:06

score 85 · Accepted Answer · edited Aug 13 '15 at 19:15

85

I sometimes use NULLIF like so...

IF NULLIF(@PreviousStartDate, '') IS NULL

There's probably no reason it's better than the way suggested by @Oded and @bluefeet, just stylistic preference.

@danihp's method is really cool but my tired old brain wouldn't go to COALESCE when I'm thinking is null or empty :-)

edited Aug 13 '15 at 19:15

Mike G

4,232
9
40
66

answered Mar 24 '12 at 20:40

Rex Miller

2,706
1
19
26

3

Hi Rex, since I have read your answer, I moved to your notation. – dani herrera Apr 24 '12 at 13:31
What if the parameter is unnamed ? – Stephane Mar 11 '22 at 09:57

Oded · Answer 2 · 2012-12-20T09:44:14.190

47

Here is the general pattern:

IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')

'' is an empty string in SQL Server.

edited Dec 20 '12 at 09:44

answered Mar 24 '12 at 20:33

Oded

489,969
99
883
1,009

score 11 · Answer 3 · answered Mar 24 '12 at 20:34

11

I use coalesce:

IF ( COALESCE( @PreviousStartDate, '' ) = '' ) ...

answered Mar 24 '12 at 20:34

dani herrera

48,760
8
117
177

Taryn · Answer 4 · 2012-12-19T23:36:50.273

6

you can use:

IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')

edited Dec 19 '12 at 23:36

answered Mar 24 '12 at 20:33

Taryn

242,637
56
362
405

1

@pavel thanks for pointing out the missin @ sign but you can see from the time stamp they were posted at the same time. – Taryn Dec 19 '12 at 23:38

score 4 · Answer 5 · answered Dec 19 '12 at 22:58

4

What about combining coalesce and nullif?

SET @PreviousStartDate = coalesce(nullif(@PreviousStartDate, ''), '01/01/2010')

answered Dec 19 '12 at 22:58

Pavel Hodek

14,319
3
32
37

Erik K. · Answer 6 · 2013-07-30T19:55:47.140

4

Another option:

IF ISNULL(@PreviousStartDate, '') = '' ...

see a function based on this expression at http://weblogs.sqlteam.com/mladenp/archive/2007/06/13/60231.aspx

edited Jul 30 '13 at 19:55

answered Jul 30 '13 at 19:49

Erik K.

1,024
12
13

score 3 · Answer 7 · answered Jun 16 '16 at 18:06

3

To check if variable is null or empty use this:

IF LEN(ISNULL(@var, '')) = 0

answered Jun 16 '16 at 18:06

Fábio Nascimento

2,644
1
21
27

For MSSQL15 and a DATETIME variable, it doesn´t work: --DECLARE @var DATETIME; --SET @var='' --SET @var=NULL --SET @var='2020-06-13 22:30:00.000' --IF LEN(ISNULL(@var, '')) = 0 --PRINT 'is null or empty'; -- #same result for all cases – danalif Jun 14 '20 at 00:52

score 2 · Answer 8 · answered Dec 15 '14 at 07:02

If you want to use a parameter is Optional so use it.

CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
    AS
    SELECT *
    FROM AdventureWorks.Person.Address
    WHERE City = ISNULL(@City,City)
    AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'
    GO

score 1 · Answer 9 · answered Nov 02 '20 at 05:42

1

To check if variable is null or empty use this

IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')

answered Nov 02 '20 at 05:42

Madhusanka Edirimanna

1,318
10
9

score 0 · Answer 10 · edited Feb 15 '18 at 18:15

0

You can try this:-

IF NULLIF(ISNULL(@PreviousStartDate,''),'') IS NULL
SET @PreviousStartdate = '01/01/2010'

edited Feb 15 '18 at 18:15

rchau

535
9
32

answered Dec 06 '13 at 09:00

TracyT

1

score 0 · Answer 11 · edited Jan 12 '16 at 16:26

0

If you want a "Null, empty or white space" check, you can avoid unnecessary string manipulation with LTRIM and RTRIM like this.

IF COALESCE(PATINDEX('%[^ ]%', @parameter), 0) > 0
    RAISERROR ...

edited Jan 12 '16 at 16:26

Luke Girvin

13,221
9
64
84

answered Oct 20 '14 at 09:52

Jodrell

34,946
5
87
124

Isn't this a performance issue when doing thousands of selects per minute? – Mecanik Sep 19 '22 at 07:51
@Mecanik, it would effect performance, to some unmeasured extent. I'd try to prevent empty or whitespace from being inserted and keep the repeated selects simple. This answer is different to the others, in that, it checks for whitespace and not just empty. – Jodrell Sep 20 '22 at 07:03

score 0 · Answer 12 · answered Mar 24 '12 at 20:40

0

I recommend checking for invalid dates too:

set @PreviousStartDate=case ISDATE(@PreviousStartDate) 
    when 1 then @PreviousStartDate 
        else '1/1/2010'
    end

answered Mar 24 '12 at 20:40

John Dewey

6,985
3
22
26

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

12 Answers12

Linked