Check if a parameter is null or empty in a stored procedure
SqlSql Server-2008Stored ProceduresParametersNullSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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 :-)
Solution 2 - Sql
Here is the general pattern:
IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')
'' is an empty string in SQL Server.
Solution 3 - Sql
I use coalesce:
IF ( COALESCE( @PreviousStartDate, '' ) = '' ) ...
Solution 4 - Sql
you can use:
IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')
Solution 5 - Sql
What about combining coalesce and nullif?
SET @PreviousStartDate = coalesce(nullif(@PreviousStartDate, ''), '01/01/2010')
Solution 6 - Sql
Another option:
IF ISNULL(@PreviousStartDate, '') = '' ...
see a function based on this expression at http://weblogs.sqlteam.com/mladenp/archive/2007/06/13/60231.aspx
Solution 7 - Sql
To check if variable is null or empty use this:
IF LEN(ISNULL(@var, '')) = 0
Solution 8 - Sql
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
Solution 9 - Sql
To check if variable is null or empty use this
IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')
Solution 10 - Sql
I recommend checking for invalid dates too:
set @PreviousStartDate=case ISDATE(@PreviousStartDate)
when 1 then @PreviousStartDate
else '1/1/2010'
end
Solution 11 - Sql
You can try this:-
IF NULLIF(ISNULL(@PreviousStartDate,''),'') IS NULL
SET @PreviousStartdate = '01/01/2010'
Solution 12 - Sql
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 ...