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

Question

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:

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

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.

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.

Script:

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

Answer 1

You've got some real problems with your understanding of SQL and I seriously doubt that temp tables are relevant.

1) Variables are initialized as null, but that doesn't appear to have significantly messed you up. (@courtFC + 0 doesn't evaluate you way you probably were thinking.)

2) The way you were doing assignments is dependent on order and the last one wins exactly as you noticed. Instead of saying:

select @court = b.court, ...

You could have use this:

select @courtFC = count(b.court) ... where b.court = 'federal court'

It also appears you're trying to write a loop and I think that's another part of your confusion. SQL is about sets, operating on multiple rows at once.

3) All of your EXISTS subqueries in the inner blocks are missing filters on the relevant caseid.

Your approach actually might work with those changes alone.

My version here isn't intended to complicate things but I fear you'll have trouble with it. Here's a set-based solution that really a more natural way.

if @caseid is not null /* short-circuit the update, but probably not necessary */
update cbm_case
set shepardsflag = coalesce(
    (
    select
        case
            max( /* highest precendence wins */
                case /* map annotations by precedence i.e. */
                     /* if there are multiple annotations, */
                     /* which one takes priority */
                    when a.annotation in ('Refd', 'Comp')     then 'P1'
                    when a.annotation in ('Foll', 'Aff')      then 'P2'
                    when a.annotation in ('Not Foll', 'Dist') then 'P3'
                    else cast(0/0 as char(2)) /* error, I think */
                end
            )
            when 'P1' then 4 /* translate back to shepardsflag */
            when 'P2' then 3
            when 'P3' then 2
        end
    from
        cba_annot as a inner join cbm_case as refcase
            on refcase.caseid = a.ref_caseid
    where
        a.src_caseid = cbm_case.caseid
        and (
            cbm_case.court <> 'federal court'  /* all if src case not federal */
            or refcase.court = 'federal court' /* always get federal */
        )
    ),
    5
)
where caseid = @caseid;

Edit 2 Ignore my comment in 3). Looking again I think I had misread OP's code because of the parenthesis and line break.

Edit 3 Fixed error caused by missing @ character in first line.