Handle NULL Value (comma Separated) Parameters in Stored Procedure where condition in SQL Server

alibasha
 
on Apr 20, 2021 11:09 PM
772 Views

Hi,

How to handle NULL and with Value (comma Separated) Parameters in where condition in SP

Based on this ulr https://blog.sqlauthority.com/2017/06/18/write-case-statement-clause-interview-question-week-127/

I wrote

SubRegionCode=
    CASE WHEN (@SubRegionCode IS NOT NULL AND @SubRegionCode!='') THEN @SubRegionCode
    ELSE SubRegionCode
END

And based on this article 

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

(SubRegionCode=@SubRegionCodeor ISNULL(@SubRegionCode,'')='')

Both are working if I have values like @SubRegionCode=N'EURA' and @SubRegionCode=N'', but result is coming as 0 (not working) if I have values as @SubRegionCode=N'EURA,US'.

That is if we pass 2 values from FE, its not working.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
alibasha
 
on Apr 27, 2021 04:00 AM

Hi, I am able to resolve the issue, using Split function. Below is the detailed code.

CREATE PROC [dbo].[sp_RPT_MISProjectListReport]         
(        
 @QuotDateFrom varchar(30)=NULL,        
 @QuotDateTo varchar(30)=NULL,        
 @SubRegionCode VARCHAR(12)=NULL            
) 
Select ProjName, Desc where  from  tbl_RPT_MISProjects         
                            where QuotDate <=     
     CASE WHEN (@QuotDateTo IS NOT NULL AND @QuotDateTo != '')   THEN   @QuotDateTo    
     ELSE QuotDate END    
 and QuotDate >=     
     CASE WHEN (@QuotDateFrom IS NOT NULL AND @QuotDateFrom != '') THEN   @QuotDateFrom    
     ELSE QuotDate END    
   AND   
     (SubRegionCode IN (SELECT value FROM STRING_SPLIT(@SubRegionCode,',')) or ISNULL(@SubRegionCode,'')='')      
                            order by GSP_CHF DESC