How to return condition-based value sets in SQL Server 2005?

Question

I am new to SQL Server 2005 stored procedure. It seems that I cannot work as I wanted.

I have a sp that takes an @caseid parameter from a table named annot . @caseid assigned the value of the src_caseid column and may have multiple references ( ref_caseid ) or not in the annot table. I want to set a condition and set the correct shepardsflag flag depending on the court column from the case table that I used with INNER JOIN.

This is basically a scenario:

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

An example result set from an INNER JOIN on ref_caseid = caseid as follows:

 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 

Installation Condition:

From a set of records, if a federal court exists, it should only accept federal court lines. If found NO federal court , then other cases will have different meanings.

To achieve this, I set a counter for federal court counts. But it seems that SQL only reads the last line and sets @courtFC to it. I tried order by but it doesn't seem to work.

From the above example, the final value of the shepards sign of flag 17338 should be = 3 (Foll), since it should only take lines with the "federal court" AND ignore the rest of the lines.

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

I hope I explain well.

Can someone please help me on the right logic? Should I create a temporary 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 have some real problems understanding SQL, and I seriously doubt that temporary tables matter.

1) Variables are initialized as null, but this does not seem to confuse you much. (@courtFC + 0 does not evaluate you as you probably thought.)

2) The way you completed the tasks depends on the order, and the latter wins exactly as you noticed. Instead of saying:

 select @court = b.court, ... 

You could use this:

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

It also seems like you're trying to write a loop, and I think that is another part of your confusion. SQL is a lot of sets that work with multiple rows at once.

3) . All EXISTS subqueries in indoor units bypass filters in the corresponding caseid.

Your approach can actually only work with these changes.

My version here is not intended to complicate things, but I'm afraid you will have problems with it. Here is a dial-based solution that is really in 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 ie */ /* 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 the wrong OP code due to parentheses and line breaks.

Edit 3 Fixed a bug caused by the absence of the @ symbol in the first line.