January 27, 2011 at 12:53 pm
reder (1/27/2011)
I was told to use the "nolock" to prevent a data lock.. ?? So far it has not been a problem with the other stored procs.
Yet.
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Yes the problem is when the @caseNbr paramenter is populated with more than one case number.
Yes, so you explained. What piece of code is affected? The IF is not affected, you said the ELSE was working fine, so what isn't working and exactly what code is affected?
As I said earlier, you will need a split function or to split the parameter into a temp table if there are multiple values in 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
January 27, 2011 at 1:05 pm
The @caseNbr is not null part is not working correctly. I get back data for the first caseNbr keyed in by the user and not the second.
the paramenter is defined as "@caseNbr char(12)" - I'm thinking this is the problem but i do not how to correct it to read the next 12 characters for the next casenbr. In the profiler results both case numbers are being sent..
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
....
January 28, 2011 at 9:44 am
GilaMonster (1/27/2011)
reder (1/27/2011)
.As I said earlier, you will need a split function or to split the parameter into a temp table if there are multiple values in it.
And thats the part I do not know how to do.... Where can I find an example? I'm not having much luck with goggle.
Thank you again for your help.
January 28, 2011 at 9:54 am
The char(12) is part of the problem. All the values are placed into that as a single comma-delimited string (it's not an array). Hence you need to make that a lot larger. Varchar(1000) would probably be large enough.
As for the split, let me round up some help...
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
January 28, 2011 at 9:59 am
Click here for the latest Delimited Split Function. By far, the absolute best high-performing split function available.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 28, 2011 at 9:59 am
There's a good article on string splitting (and related Numbers table functions), here: http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 9:59 am
Here's a split function (and much more).
http://www.sqlservercentral.com/articles/T-SQL/62867/
-- Gianluca Sartori
January 28, 2011 at 11:13 am
thank you everyone for your help! I will work with all suggestions, one of them is sure to work for what I need.
thanks again!! 🙂
January 28, 2011 at 2:36 pm
You can't get more than 1 case number into a 12 character field. That really would be magic! You need to change that parameter type (maybe change its name too, as in the body of the proc your using the current name for a single case number) so that it's long enough to accept however many case numbers you want to allow the user to provide, and then you can use a split function to get a result set (or temp table, or table variable, or whatever) with individual case numbers in it and carry on from there.
Tom
February 2, 2011 at 11:44 am
Hello everyone - Thought i would share my solution. I used a function that someone pointed me to and a cursor to create the file of case numbers..
Life is good.. thanks so much for all your help! 🙂
IF (@caseNbr is not null )
BEGIN -- get all the active parties on the cases entered
declare @caseNbrs varchar(900)
,@seqno int
,@item char(12)
set @caseNbrs = @caseNbr
--
create table #tempFileA
(CaseID int
,CaseNbr char(12)
,CaseUTypeID int
,div varchar(30)
,CasePartyID int
,nameid int
,PartyID int)
Declare CaseNbr_cursor CURSOR for
SELECT item FROM dbo.uftSplitString(@caseNbrs,',')
Open CaseNbr_cursor
Fetch next from CaseNbr_cursor into @item
WHILE @@FETCH_STATUS = 0
BEGIN
insert #tempFileA
select distinct
cah.CaseID
,cah.CaseNbr
,cch.CaseUTypeID
,od.OrgUnitName
,cp.CasePartyID
,cp.nameid
,cp.PartyID
from Justice..ClkCaseHdr cch
join justice..CaseAssignHist cah on cah.caseid = cch.CaseID
join operations..fnGetNodeList('FLHILLSBTEST')od on od.NodeID = cah.NodeID
join Justice..CaseParty cp on cp.CaseID = cah.CaseID
join Justice..CasePartyConn cpc on cp.CasePartyID = cpc.CasePartyID
join Justice..CasePartyConnStat cpcs on cpcs.CasePartyConnID = cpc.CasePartyConnID
where cah.CaseNbr = @item and cpcs.Inactive = 0 and cah.JudgeID is not null
Fetch next from CaseNbr_cursor into @item
END
CLOSE CaseNbr_cursor
DEALLOCATE CaseNbr_cursor
-- the following figures out who is the plaintiff or defendant attorney
select ....etc... the rest of the code that I posted is the same ....
February 2, 2011 at 1:48 pm
Errrr.... why the cursor?
IF ( @caseNbr IS NOT NULL )
BEGIN -- get all the active parties on the cases entered
CREATE TABLE #tempFileA (
CaseID INT ,
CaseNbr CHAR(12) ,
CaseUTypeID INT ,
div VARCHAR(30) ,
CasePartyID INT ,
nameid INT ,
PartyID INT
);
INSERT #tempFileA (CaseID,CaseNbr,CaseUTypeID, div, CasePartyID, nameid, PartyID)
SELECT DISTINCT
cah.CaseID ,
cah.CaseNbr ,
cch.CaseUTypeID ,
od.OrgUnitName ,
cp.CasePartyID ,
cp.nameid ,
cp.PartyID
FROM Justice..ClkCaseHdr cch
JOIN justice..CaseAssignHist cah ON cah.caseid = cch.CaseID
JOIN operations..fnGetNodeList('FLHILLSBTEST') od ON od.NodeID = cah.NodeID
JOIN Justice..CaseParty cp ON cp.CaseID = cah.CaseID
JOIN Justice..CasePartyConn cpc ON cp.CasePartyID = cpc.CasePartyID
JOIN Justice..CasePartyConnStat cpcs ON cpcs.CasePartyConnID = cpc.CasePartyConnID
WHERE cah.CaseNbr IN (SELECT item FROM dbo.uftSplitString(@caseNbrs, ',') )
AND cpcs.Inactive = 0
AND cah.JudgeID IS NOT NULL
-- the following figures out who is the plaintiff or defendant attorney... etc, etc, etc
END
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
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply