Using Case To Control Program Flow ...

I'm writing my first serious stored procedure.

Essentially I have an incoming file, each line in the file is a record.
The records share the same initial key fields for the first 10 columns, then the field structure varies depending on a rectype and sequence number.

My initial plan was load the keys into fields, and load the remaining data into a long varchar field.

Then the stored procedure would evaluate the Rectype and Seqno of each record and chop up the Varchar accordingly.

So I set up a cursor to read the temporary table, do a fetch into variables, and go to evaluate the variables.

I want to be able to use a CASE statement to evaluate the fields and then perform various logic, but it's giving me fits because it seems like CASE only really works in Select statements, and won't really allow you to do any sort of GOTO logic.

I chopped the following SQL up and put in a rough cut of what I thought I was doing.

DROP TABLE #FOO
GO
CREATE TABLE #FOO (
[planno] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssn] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[location] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eligdate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rectype] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[seqno] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[empno] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[payrollcode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companycode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[department] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler] [char] (34) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[data] [varchar] (366) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

BULK INSERT #FOO FROM '\l32b0021foo.dat'
WITH (FORMATFILE = '\l32b0021foo.fmt')

declare @sessionid varchar(12)
declare @dedcode varchar(5)
declare @eeamt money
declare @eepct decimal
declare @effdt datetime

set @effdt = getdate()
set @sessionid = 'PENDING_BI'


DECLARE Transaction_Cursor CURSOR FOR
SELECT ssn,rectype,seqno,data
from #foo

OPEN Transaction_Cursor

declare @eepssn varchar(9)
declare @rectype varchar(3)
declare @seqno varchar(3)
declare @data varchar(366)
declare @eeccoid varchar(6)
declare @eeceeid varchar(12)
declare @eecempno varchar(9)
declare @companycode varchar(5)
declare @type varchar(20)


FETCH NEXT FROM Transaction_Cursor into @eepssn,@rectype,@seqno,@data

WHILE @@FETCH_STATUS = 0
BEGIN
select @companycode = cmpcompanycode,
@eeccoid = eeccoid,
@eeceeid = eeceeid,
@eecempno = eecempno
from company,empcomp
where eeceeid = (select eepeeid from emppers where eepssn = @eepssn )
and eecemplstatus = 'A'
and cmpcoid = eeccoid




CASE @RECTYPE+@SEQNO
when '001001' then goto parse_pretax
when '002001' then goto parse_LOAN
else select @RECTYPE+@SEQNO+' not recognized!'
end
process_it:
insert into foo2 (empno,companycode,amt,pct)
values (@eecempno,@companycode,@eeamt,@eepct)
FETCH NEXT FROM Transaction_Cursor into @eepssn,@rectype,@seqno,@data
END

CLOSE Transaction_Cursor
DEALLOCATE Transaction_Cursor

goto bypass

parse_pretax:
let @eepct = substring(@data,1,5)

goto process_it

parse_loan:
let @eeamt = substring(@data,27,11)
goto process_it

bypass:


I could sketch it out a little bit better in Northwind or Pubs, but I think I just need a smack upside the head and a little edification.