Check if a parameter is null or empty in a stored

问题:

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.

回答1:

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 :-)

回答2:

Here is the general pattern:

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

'' is an empty string in SQL Server.

回答3:

I use coalesce:

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

回答4:

What about combining coalesce and nullif?

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

回答5:

you can use:

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

回答6:

Another option:

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

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

回答7:

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

回答8:

To check if variable is null or empty use this:

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

回答9:

I recommend checking for invalid dates too:

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

回答10:

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

回答11:

You can try this:-

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