• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

SQL query

Experts,
I have pasted a piece from my stored procedure here. When @ssid id is not null but @RegionID is null there are values in DB that should be returned but this query does not. And same way if there are results for @RegionID is not null then there are results in Db but this query does not return those. I want it to work in any situation whether all the three variables are null or not it should display proper results. Can that be achieved ..?
declare @ssid varchar(max)
declare @stid varchar(max) 
declare @RegionID varchar(10) 
 
set @ssid='acd5eb63-6cf5-4ae0-9d26-afa76f2cd83f'
set @stid=''
set @RegionID=''
 
 
select distinct  cstitle as PageTitle,substring(convert(varchar(4000),csChallenge),0,150)+'...' as Excerpt,'Client Success' as AreaName,'' 
as OfficeName,convert(varchar(38),tbl_CS_Master.csID) as csid                               
,'' as ksID                
,'' as "Filename"            
,'' as ks_FName ,'' as ks_LName                      
from tbl_CS_Master left  join tbl_cs_service on tbl_cs_service.csid=                                  
tbl_CS_Master.csid left  join  tbl_CS_Regions on tbl_CS_Master.csID = tbl_CS_Regions.csID where 
(convert(varchar(36),tbl_cs_service.ssid) in                                   
(select cast(value as uniqueidentifier) from dbo.ParmsToList(@ssid)) or @ssid is null) 
and (tbl_CS_Regions.RegionID in (select cast( value as int) from dbo.ParmsToList(@RegionID)) or @RegionID is null)

Open in new window

0
dotnetpro
Asked:
dotnetpro
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this

select distinct
        cstitle as PageTitle,
        substring(convert(varchar(4000), csChallenge), 0, 150) + '...' as Excerpt,
        'Client Success' as AreaName, '' as OfficeName,
        convert(varchar(38), tbl_CS_Master.csID) as csid, '' as ksID,
        '' as "Filename", '' as ks_FName, '' as ks_LName
from    tbl_CS_Master
left  join tbl_cs_service on tbl_cs_service.csid = tbl_CS_Master.csid
left  join tbl_CS_Regions on tbl_CS_Master.csID = tbl_CS_Regions.csID
where   (  @ssid is null or convert(varchar(36), tbl_cs_service.ssid) in
         (
          select    cast(value as uniqueidentifier)
          from      dbo.ParmsToList(@ssid) )
           )
        and ( @RegionID is null OR    tbl_CS_Regions.RegionID in ( select   cast(value as int)
                                           from     dbo.ParmsToList(@RegionID) )
                )
0
 
Kevin CrossChief Technology OfficerCommented:
I believe the conditions are read in order so try like this:
declare @ssid varchar(max)
declare @stid varchar(max) 
declare @RegionID varchar(10) 
 
set @ssid='acd5eb63-6cf5-4ae0-9d26-afa76f2cd83f'
set @stid=''
set @RegionID=''
 
 
select distinct  cstitle as PageTitle,substring(convert(varchar(4000),csChallenge),0,150)+'...' as Excerpt,'Client Success' as AreaName,'' 
as OfficeName,convert(varchar(38),tbl_CS_Master.csID) as csid                               
,'' as ksID                
,'' as "Filename"            
,'' as ks_FName ,'' as ks_LName                      
from tbl_CS_Master left  join tbl_cs_service on tbl_cs_service.csid=                                  
tbl_CS_Master.csid left  join  tbl_CS_Regions on tbl_CS_Master.csID = tbl_CS_Regions.csID 
where (@ssid is null or convert(varchar(36),tbl_cs_service.ssid) in                                   
(select cast(value as uniqueidentifier) from dbo.ParmsToList(@ssid))) 
and (@RegionID is null or tbl_CS_Regions.RegionID in (select cast( value as int) from dbo.ParmsToList(@RegionID)))

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
I noticed that you set @RegionID = '' in the procedure; therefore, maybe your issue is not that it is NULL but that it is empty string.  Try this:
declare @ssid varchar(max)
declare @stid varchar(max) 
declare @RegionID varchar(10) 
 
set @ssid='acd5eb63-6cf5-4ae0-9d26-afa76f2cd83f'
set @stid=''
set @RegionID=''
 
 
select distinct  cstitle as PageTitle,substring(convert(varchar(4000),csChallenge),0,150)+'...' as Excerpt,'Client Success' as AreaName,'' 
as OfficeName,convert(varchar(38),tbl_CS_Master.csID) as csid                               
,'' as ksID                
,'' as "Filename"            
,'' as ks_FName ,'' as ks_LName                      
from tbl_CS_Master left  join tbl_cs_service on tbl_cs_service.csid=                                  
tbl_CS_Master.csid left  join  tbl_CS_Regions on tbl_CS_Master.csID = tbl_CS_Regions.csID 
where (ISNULL(@ssid, '') = '' or convert(varchar(36),tbl_cs_service.ssid) in                                   
(select cast(value as uniqueidentifier) from dbo.ParmsToList(@ssid))) 
and (ISNULL(@RegionID, '') = '' or tbl_CS_Regions.RegionID in (select cast( value as int) from dbo.ParmsToList(@RegionID)))

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now