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.

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

83

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,142
9
43
64

answered Mar 24 '12 at 20:40

Rex Miller

2,666
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

43

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

477,625
97
867
998

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

44,444
7
103
165

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

234,956
54
359
399

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

13,589
3
31
37

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

3

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.

974
11
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,479
1
20
26

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,049
7
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

515
8
31

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

12,913
8
60
81

answered Oct 20 '14 at 09:52

Jodrell

32,967
4
79
120

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,868
3
21
26

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

12 Answers12

Linked