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

User · Answer

You can try this -      IF NULLIF ISNULL  PreviousStartDate         IS NULL SET  PreviousStartdate    01 01 2010

User · Answer

To check if variable is null or empty use this   IF LEN ISNULL  var         0

User · Answer

I recommend checking for invalid dates too   set  PreviousStartDate case ISDATE  PreviousStartDate       when 1 then  PreviousStartDate          else  1 1 2010      end

User · Answer

To check if variable is null or empty use this IF  PreviousStartDate IS NULL OR  PreviousStartDate

User · Answer

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  -

User · Answer

What about combining coalesce and nullif   SET  PreviousStartDate   coalesce nullif  PreviousStartDate        01 01 2010

User · Answer

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   gt  0     RAISERROR

User · Answer

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

User · Answer

you can use   IF  PreviousStartDate IS NULL OR  PreviousStartDate

User · Answer

Here is the general pattern   IF  PreviousStartDate IS NULL OR  PreviousStartDate            is an empty string in SQL Server

User · Answer

I use coalesce   IF   COALESCE   PreviousStartDate

User · Answer

Another option   IF ISNULL  PreviousStartDate                 see a function based on this expression at http   weblogs sqlteam com mladenp archive 2007 06 13 60231 aspx

[sql] Check if a parameter is null or empty in a stored procedure

Examples related to sql

Examples related to sql-server-2008

Examples related to stored-procedures

Examples related to parameters

Examples related to null