Handle NULL and comma Separated Parameters in Stored Procedure where condition in SQL Server

Question

HiHow to handle NULL and with Value comma Separated Parameters in where condition in SPBased on this ulr httpsblogsqlauthoritycom20170618writecasestatementclauseinterviewquestionweek127I wroteSubRegionCode    CASE WHEN SubRegionCode IS NOT NULL AND SubRegionCode3939 THEN SubRegionCode    ELSE SubRegionCodeENDAnd based on this article Return all records when Parameter is Blank Empty or Null in SQL Server QuerySubRegionCodeSubRegionCodeor ISNULLSubRegionCod

alibasha · Accepted Answer

HiI am able to resolve the issue using Split functionBelow is the detailed codeCREATE PROC dbospRPTMISProjectListReport                     QuotDateFrom varchar30NULL            QuotDateTo varchar30NULL            SubRegionCode VARCHAR12NULL             Select ProjName Desc where  from  tblRPTMISProjects         where QuotDate lt          CASE WHEN QuotDateTo IS NOT NULL AND QuotDateTo  3939   THEN   QuotDateTo         ELSE QuotDate END    and QuotDate gt          CASE WHEN QuotDateFrom IS NOT NULL AND QuotDateFrom  3939 THEN   QuotDateFrom         ELSE QuotDate END    AND        SubRegionCode IN SELECT value FROM STRINGSPLITSubRegionCode3939 or ISNULLSubRegionCode39393939      order by GSPCHF DESC

Handle NULL and comma Separated Parameters in Stored Procedure where condition in SQL Server

Return all records when Parameter is Blank (Empty) or Null in SQL Server Query

Company

Explore

Follow us

Contact Us