Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

How to return sets of values based on condition in SQL Server 2005?

FifahFifah Posts: 2Member
I am a beginner in SQL Server 2005 stored procedure. I can't seem to get it working as wanted.

I have a sp that takes in parameter @caseid from a table called annot. @caseid is assigned to value of column src_caseid and can have multiple references (ref_caseid) or none in table annot. I want to set a condition and set proper shepardsflag depending on the column court from table case which I did using INNER JOIN.

Basically this is the scenario:

Table annot - ref_caseid, src_caseid, annotation
Table case - caseid, court

Example of set of results from the INNER JOIN on ref_caseid = caseid like this:
[code]
ref_caseid src_caseid annotation court
17334 17338 Refd high court
17600 17338 Foll federal court
18271 17338 Foll federal court
43220 17338 Not Foll supreme court
[/code]
Condition to be set:

From the recordset, if federal court exists, it should only take rows of federal court. If NO federal court is found, then it will take other cases with other court values.

To achieve this, I set a counter for federal court counts. But seems that SQL only reads the last row and set @courtFC value based on it. I've tried order by but doesn't seem working as well.

From sample above, the final shepardsflag value of case 17338 should be = 3 (value of @shep for 'Foll') as it should take rows with "federal court" only AND ignore the rest of the rows.

But the current result is shepardsflag = 2 ; which is wrong

I hope I explain well.

Can someone please help me on the right logic? Should I create a temp table? Thanks in advance.

[b]Script:[/b]

[code]
ALTER PROCEDURE [dbo].[spUpdateShepardsFlags] @caseid int = null AS
begin
declare @Shep int
declare @ref_caseid int
declare @court int
declare @courtFC int
declare @annot int

if @caseid is not null
begin
select @court = b.court, @ref_caseid = a.ref_caseid, @annot = a.annotation
from cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid
where a.src_caseid = @caseid

if @court is not null
begin
if @court = 'MYFC'
set @courtFC = @courtFC + 1
if @court <> 'MYFC'
SET @courtFC = @courtFC + 0
PRINT 'The @courtFC counter : ' + CAST(@courtFC AS CHAR)
end

if @court is not NULL
begin
if @courtfc > 0
begin
if exists(select a.ref_caseid, b.court, a.annotation, a.src_caseid from
cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid)
begin
if exists(select src_caseid from cba_annot where (annotation like '%Refd%'
or annotation like '%Comp%')
and src_caseid = @caseid)
set @Shep = 4

if exists(select src_caseid from cba_annot where (annotation like '%Foll%'
or annotation like '%Aff%')
and src_caseid = @caseid)
set @ShepFC = 3

update cbm_case
set shepardsflag = @shep
where caseid=@caseid
end
end

else -- if @courtFC = 0
begin --new
if exists(select a.ref_caseid, b.court, a.annotation, a.src_caseid from
cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid)
begin
if exists(select src_caseid from cba_annot where (annotation like '%Refd%'
or annotation like '%Comp%')
and src_caseid = @caseid)
set @Shep = 4

if exists(select src_caseid from cba_annot where (annotation like '%Foll%'
or annotation like '%Aff%')
and src_caseid = @caseid)
set @Shep = 3

if exists(select src_caseid from cba_annot where (annotation like '%Not Foll%'
or annotation like '%Dist%')
and src_caseid = @caseid)
set @Shep = 2

update cbm_case
set shepardsflag = @shep
where caseid=@caseid
end

end -- new
end
else --- if court is NULL -- case not referred by any other case
update cbm_case
set shepardsflag = 5
where caseid=@caseid
end

else -- if caseid is null

-- other condition
[/code]
Sign In or Register to comment.