Input data with mulit records for a parameter.

Question

Post reply

Input data with mulit records for a parameter.

reder

SSC Enthusiast

Points: 141
More actions
January 27, 2011 at 9:50 am

#234031

I'm sure this is an easy one for all you experts out there... I'm a bit new to this and hope someone can help me.
My input data is coming from a SSRS report and one of the parameters can have multiple entries or none. It works like this..
ALTER PROCEDURE [dbo].[rpt_CaseFolderLabels]
@startDate date
,@endDate date
,@court char(02)
,@location char(02)
,@caseNbr char(12)
,@filingFee int
AS
IF (@caseNbr <> null or @caseNbr <> ' ')
BEGIN ....
Now if @caseNbr is null or blank, the stored proc goes to the "else" and gets the information for the labels based on all the other parameters - no problem that works. If the user keys in ONE caseNbr no problem ... if they key in more than one caseNbr I get data for the first @caseNbr and not the others.
All suggestions will be a great help!
thank you!
Martha

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Lots of unanswered questions

1. Do the users separate case numbers with commas?

2. Do the users separate case numbers input with one or more spaces?

3. Do valid case numbers contain spaces for example "134 A6"

To help those who want to help you, please input table definitions, sample data (typical but NOT ACTUAL. ) You can do this simply and quickly by following the article that can be accessed by clicking on the first link in my signature block

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 2

Edit: double post

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 3

edit: duplicate post reply moved to the "twin post"

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

reder SSC Enthusiast Points: 141 More actions · Answer 4

The data for the multiple records for @caseNbr will look like this:

12-XR-012345, 12-RT-098733, 09-CA-123456

The user can input 3 case numbers or 20. And the case number is six character.

Thank you for your help.

reder SSC Enthusiast Points: 141 More actions · Answer 5

Sorry i did not realize I posted this twice... where is the original?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

1) You don't do = or <> checks with NULL. Do IS NULL or IS NOT NULL

2) http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

reder SSC Enthusiast Points: 141 More actions · Answer 7

Gail

I corrected my "IF" to

IF (@caseNbr is not null)

Begin

...

And I still only get one label printer.

thank you for your help

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Yes, you will. I was just pointing out a logic error.

Run profiler and see what SSRS is sending to SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

reder SSC Enthusiast Points: 141 More actions · Answer 9

This is the results of the profiler:

exec rpt_CaseFolderLabels @caseNbr=N'10-cc-033227,10-cc-032468',@startDate='2011-01-27 00:00:00',@endDate='2011-01-27 00:00:00',@court=N'CA',@location=N'TA',@filingFee=0

The two @caseNbr entered are received. the other parameters are ignored because the @caseNbr is not null.

reder SSC Enthusiast Points: 141 More actions · Answer 10

This the code for if the @caseNbr is not null:

ALTER PROCEDURE [dbo].[rpt_CaseFolderLabels]

@startDate date

,@endDate date

,@court char(02)

,@location char(02)

,@caseNbr char(12)

,@filingFee int

AS

IF (@caseNbr is not null)

BEGIN -- get all the active parties on the case

select distinct

cah.CaseID

,cah.CaseNbr

,cch.CaseUTypeID

,od.OrgUnitName as Div

,cp.CasePartyID

,cp.nameid

,cp.PartyID

into #tempFileA

from Justice..ClkCaseHdr cch with (nolock)

join justice..CaseAssignHist cah with (nolock) on cch.CaseID = cah.CaseID

join operations..fnGetNodeList('FLHILLSBTEST')od on od.NodeID = cah.NodeID

join Justice..CaseParty cp with (nolock) on cp.CaseID = cah.CaseID

join Justice..CasePartyConn cpc with (nolock) on cp.CasePartyID = cpc.CasePartyID

join Justice..CasePartyConnStat cpcs on cpcs.CasePartyConnID = cpc.CasePartyConnID

where cah.CaseNbr = @caseNbr and cpcs.Inactive = 0 and cah.JudgeID is not null

-- the following will match up the attorneys to the defendant or plaintiff

select distinct

t.CaseID

,t.CaseNbr

,t.CaseUTypeID

,t.Div

,cs.SessionDate as 'preTrialDate'

,cs.StartTime as 'preTrialTime'

,t.CasePartyID as TpartyID

,cpa.CasePartyID

,cpa.AttyCasePartyID

,uc.Code

,nam.NameFirst

,nam.NameMid

,nam.NameLast

into #tempFileA1

from #tempFileA t

join justice..HearingEvent he with (nolock)

on t.CaseID = he.CaseID

join justice..setting s with (nolock)

on s.hearingid = he.hearingid and he.hearingID = (select MAX(hearingID) from Justice..HearingEvent jhe

where jhe.CaseID = t.CaseID)

join justice..CtSessionBlock csb with (nolock)

on s.CourtSessionBlockId = csb.CourtSessionBlockId and csb.deleted = 0

join justice..CtSession cs with (nolock)

on csb.CourtSessionId = cs.CourtSessionId and cs.deleted = 0

inner join Justice..CaseParty cp on cp.CaseID = t.Caseid

inner join Justice..Name nam on nam.NameID = t.NameID

inner join Justice..CasePartyConn cpc on cpc.CasePartyID = t.CasePartyID

inner join justice..uCode uc on uc.CodeID = cpc.ExtConnID

left join Justice..CasePartyAtty cpa on cpa.AttyCasePartyID = t.CasePartyID

where t.CaseID = he.CaseID

-- the following figures out who is the plaintiff or defendant attorney

select distinct

t1.CaseID

,t1.CaseNbr

,t1.CaseUTypeID

,juc.Description

,t1.Div

,t1.preTrialDate

,t1.preTrialTime

,t1.TpartyID

,cpc.BaseConnKy as BaseConnKy

,t1.Code

,t1.NameFirst

,t1.NameMid

,t1.NameLast

into #tempFileA2

from #tempFileA1 t1

left join justice..CasePartyConn cpc with (nolock) on cpc.CasePartyID = t1.CasePartyID

left join Justice..uCode juc on juc.Codeid = t1.CaseUTypeID

--- the following figures out the filing fee

select

t2.CaseID

,t2.CaseNbr

,t2.Description

,t2.Div

,t2.preTrialDate

,t2.preTrialTime

,sum(td.PaymentAmount) as filingFee

,t2.BaseConnKy

,t2.Code

,CASE when (t2.code = 'JDG') then '1'

when (t2.code in ('PLTF','PETN')) then '2'

when (t2.code in ('DEF','RESP')) then '3'

when (t2.code in ('ATTY','CONVATTY') AND t2.BaseConnKy = 'PL') THEN '4'

else '9' end as seqCount

,t2.NameFirst

,t2.NameMid

,t2.NameLast

from Financial.dbo.FeeInst fi with (nolock)

left join Financial.dbo.xFincChrgFeeInst xf with (nolock) on xf.FeeInstanceID = fi.FeeInstanceID

left join Financial.dbo.FincChrg fc with (nolock) on fc.ChargeID = xf.ChargeID

left join Financial.dbo.xCaseFincChrg xc with (nolock) on xc.ChargeID = fc.ChargeID

left join Financial.dbo.TransDet td with (nolock) on td.FeeInstanceID = fi.FeeInstanceID

left join Financial.dbo.Trans t with (nolock) on t.TransactionID = td.TransactionID

left join Financial.dbo.uCode uc with (nolock) on uc.CodeID = fi.FeeID

left join Financial.dbo.Rcpt r with (nolock) on r.ReceiptID = t.ReceiptID

left join justice.dbo.ClkCaseHdr jcch with (nolock) on jcch.CaseID = xc.CaseID

left join Justice.dbo.CaseAssignHist jcsh with (nolock) on jcsh.CaseAssignmentHistoryID = jcch.CaseAssignmentHistoryIDCur

left join #tempFileA2 t2 on t2.caseID = xc.CaseID

where r.ReceiptNumber is not null

and ISNULL(casesecgrpid,1)>0

AND jcsh.CaseNbr IN (t2.CaseNbr)

and uc.Code in

('8102','8365','8368','8370','8377','8378','8382','8383','8401','8402','8408','8409'

,'8411','8436','8437','8454','8455','8456','8457','8458','B436','C436','R000','R001'

,'R003','R006','R007','R008','R017','R018','R019','R111','R131','R132','R804')

group by

t2.CaseID,t2.CaseNbr,t2.Div,t2.preTrialDate,t2.preTrialTime,t2.TpartyID

,t2.BaseConnKy,t2.Code,t2.NameFirst,t2.NameMid,t2.NameLast,t2.Description

order by seqCount

END

Else

BEGIN ...

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

I think it's the ELSE portion that we need to see.

You're going to need to split that into a temp table or using a split function in order to use it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

reder SSC Enthusiast Points: 141 More actions · Answer 12

The else part works.. it gathers the data based on the start and end date parameters and ignores the @caseNbr parameter. Heres the "esle" part:

ELSE

BEGIN

-- get all the active parties on the case

select distinct

cah.CaseID

,cah.CaseNbr

,cch.CaseUTypeID

,od.OrgUnitName as Div

,cp.CasePartyID

,cp.nameid

,cp.PartyID

into #tempFileB

from Justice..ClkCaseHdr cch with (nolock)

join justice..CaseAssignHist cah with (nolock) on cch.CaseID = cah.CaseID

join operations..fnGetNodeList('FLHILLSBTEST')od on od.NodeID = cah.NodeID

join Justice..CaseParty cp with (nolock) on cp.CaseID = cah.CaseID

join Justice..CasePartyConn cpc with (nolock) on cp.CasePartyID = cpc.CasePartyID

join Justice..CasePartyConnStat cpcs on cpcs.CasePartyConnID = cpc.CasePartyConnID

where cah.CaseNbr like '%%'+@court+'%'

and (DtFile >= @startDate and DtFile < = @endDate) and cpcs.Inactive = 0

and cah.JudgeID is not null

-- the following will match up the attorneys to the defendant or plaintiff

select distinct

t.CaseID

,t.CaseNbr

,t.CaseUTypeID

,t.Div

,cs.SessionDate as 'preTrialDate'

,cs.StartTime as 'preTrialTime'

,t.CasePartyID as TpartyID

,cpa.CasePartyID

,cpa.AttyCasePartyID

,uc.Code

,nam.NameFirst

,nam.NameMid

,nam.NameLast

into #tempFileB1

from #tempFileB t

join justice..HearingEvent he with (nolock)

on t.CaseID = he.CaseID

join justice..setting s with (nolock)

on s.hearingid = he.hearingid and he.hearingID =

(select MAX(hearingID) from Justice..HearingEvent jhe

where jhe.CaseID = t.CaseID)

join justice..CtSessionBlock csb with (nolock)

on s.CourtSessionBlockId = csb.CourtSessionBlockId and csb.deleted = 0

join justice..CtSession cs with (nolock)

on csb.CourtSessionId = cs.CourtSessionId and cs.deleted = 0

inner join Justice..CaseParty cp with (nolock) on cp.CaseID = t.Caseid

inner join Justice..Name nam with (nolock) on nam.NameID = t.NameID

inner join Justice..CasePartyConn cpc with (nolock) on cpc.CasePartyID = t.CasePartyID

inner join justice..uCode uc with (nolock) on uc.CodeID = cpc.ExtConnID

left join Justice..CasePartyAtty cpa with (nolock)on cpa.AttyCasePartyID = t.CasePartyID

where t.CaseID = he.CaseID

-- the following figures out who is the plaintiff or defendant attorney

select distinct

t1.CaseID

,t1.CaseNbr

,t1.CaseUTypeID

,juc.Description

,t1.Div

,t1.preTrialDate

,t1.preTrialTime

,t1.TpartyID

,cpc.BaseConnKy as BaseConnKy

,t1.Code

,t1.NameFirst

,t1.NameMid

,t1.NameLast

into #tempFileB2

from #tempFileB1 t1

left join justice..CasePartyConn cpc with (nolock) on cpc.CasePartyID = t1.CasePartyID

left join Justice..uCode juc on juc.Codeid = t1.CaseUTypeID

--- the following figures out the filing fee

declare @seqCount int = 0