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

Fifah

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 (Foll) as it should take rows with "federal court" only AND ignore the rest of the rows. {please see condition below for shepardsflag value @shep)

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.

[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]

AnkitArora

you can use like this

IF EXISTS(SELECT caseid FROM case WITH(NOLOCK) where court='Feedral court' AND caseID=@caseid)
begin
--To work for Feedral
END
Else
BEGIN
--To Work for others
end