dotnetpro
asked on
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 ..?
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select distinct
cstitle as PageTitle,
substring(convert(varchar(
'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)
)