Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: 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 '\\l32b0021\foo.dat'
    WITH (FORMATFILE = '\\l32b0021\foo.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.
    Howard Nugent
    Sr. Business Application Analyst

  2. #2
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    You are right, you can only use CASE statements in SELECT statements.
    However, you can use IF .. ELSE blocks like the example in BOL ...

    IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
    BEGIN
    PRINT 'The following titles are excellent mod_cook books:'
    PRINT ' '
    SELECT SUBSTRING(title, 1, 35) AS Title
    FROM titles
    WHERE type = 'mod_cook'
    END
    ELSE
    IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15
    BEGIN
    PRINT 'The following titles are expensive mod_cook books:'
    PRINT ' '
    SELECT SUBSTRING(title, 1, 35) AS Title
    FROM titles
    WHERE type = 'mod_cook'
    END

    You could do something like:

    if (@RECTYPE+@SEQNO = '001001')
    then
    begin
    goto parse_pretax
    end
    else if (@RECTYPE+@SEQNO = '002001')
    then
    begin
    goto parse_LOAN
    end

    But I don't know whether you can use a GOTO in this context (I don't use them in SQL). I'd suugest putting common code in a stored-procedure or function instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •