>I am working on creating a stored procedure. I have not created many so I am not sure of the syntax or the most efficent way to solve the problem.
>
>What I want to do is:
>
>1) Take two Parms
>2) Insert a new record that uses those Parms
>3) Get the Identity value for the inserted record
>4) Query a table using the two parms
>5) Insert a new record into another table for each record return in step 4
>6) Update the table queried in step 4 for all results returned in step 4
>7) Return the data queried in step 4
>
>I would also like to rollback if any of the steps fail.
>
>This is how far I have gotten:
>
>

>@County VARCHAR(50), @ST CHAR(2)
>
>
>DECLARE @datetime DATETIME
>SET @datetime=GETDATE()
>
>DECLARE @cDesc VARCHAR(100)
>SET @cDesc = @County + '-' + @State + ' For SD Office'
>
>INSERT INTO CO_GROUP(CO_DATE,CO_EXPIRE,CO_DESC,CO_EMP_ID) VALUES(@datetime,@datetime+30,@cDesc,0)
>
>DECLARE @iCO_GRP INT
>
>@iCO_GRP = SCOPE_IDENTITY()
>
>DECLARE C1 CURSOR
>FOR SELECT DISTINCT consumer.idnum, res1_fn, res1_int, res1_ln, res1_suf, group_ID,
>addr1, addr2, city, state, zip, zip4, county, res1_dob, res1_Bm, res1_by,phone, OLD_POOL
>FROM consumer LEFT JOIN CON_PHONE ON consumer.idnum = CON_PHONE.IDNUM
>WHERE consumer.IDNUM > 0 AND consumer.ORIG_SOURCE <> 'RES' AND LEADSTATUS = ' '
>AND TYPE <> 9 AND TYPE <> 10 AND DNC=0 and DNCFLAG = ' '
>AND consumer.state = @ST and County = @County
>ORDER BY zip, RES1_LN, RES1_FN
>
>OPEN c1
>
>INSERT INTO CO_HIST(CO_GROUP,idnum, EMP_ID, HOLD_DATE, EMP_TYPE, DUE_BACK, MEDIA_TYPE,leadage,Agent_ID)
>SELECT @iCO_GRP,c1.idnum,CAST(0 AS INT),@datetime,CAST('T' as CHAR(1)),@datetime+30,1,c1.old_pool,CAST(555251 as INT) FROM C1
>
>UPDATE CONSUMER SET LEADSTATUS='O', HOLD_DATE= @datetime, OLD_POOL="OLD_POOL"+1 WHERE idnum in (SELECT idnum from c1)
>
>RETURN C1
>
>

CREATE PROCEDURE InsertCountryAndState(
       @County VARCHAR(50),
       @ST CHAR(2))
AS
BEGIN
   DECLARE @datetime DATETIME
   DECLARE @iCO_GRP INT
   DECLARE @cDesc VARCHAR(100)   

   SET @datetime=GETDATE()
   SET @cDesc = @County + '-' + @State + ' For SD Office'

   INSERT INTO CO_GROUP(CO_DATE,CO_EXPIRE,CO_DESC,CO_EMP_ID) VALUES(@datetime,@datetime+30,@cDesc,0)

   SET @iCO_GRP = SCOPE_IDENTITY()

   DECLARE @Temp TABLE (idnum [put the right type here], OLD_POOL int)
   INSERT INTO @Temp
   SELECT consumer.idnum,
          consumer.OLD_POOL
   FROM consumer
        WHERE consumer.IDNUM > 0            AND
              consumer.ORIG_SOURCE <> 'RES' AND
              LEADSTATUS = ' '              AND
              TYPE NOT IN (9, 10)           AND
              DNC=0                         AND
              DNCFLAG = ' '                 AND
              consumer.state = @ST          AND
              County = @County



INSERT INTO CO_HIST(CO_GROUP,idnum, EMP_ID, HOLD_DATE, EMP_TYPE, DUE_BACK, MEDIA_TYPE,leadage,Agent_ID) 
SELECT @iCO_GRP,
       idnum,
       0, -- No need to CAST
       @datetime,
       'T',-- No need to CAST
        @datetime+30,
        1,
        OLD_POOL,
        555251
FROM @Temp

UPDATE CONSUMER SET LEADSTATUS = 'O',
                    HOLD_DATE  = @datetime,
                    OLD_POOL   = OLD_POOL + 1
FROM CONSUMER 
INNER JOIN @Temp Temp ON CONSUMER.idnum = Temp.idnum
END

NOT TESTED
Especially look in that part:

   INSERT INTO @Temp
   SELECT consumer.idnum,
          consumer.OLD_POOL
   FROM consumer
        WHERE consumer.IDNUM > 0            AND
              consumer.ORIG_SOURCE <> 'RES' AND
              LEADSTATUS = ' '              AND
              TYPE NOT IN (9, 10)           AND
              DNC=0                         AND
              DNCFLAG = ' '                 AND
              consumer.state = @ST          AND
              County = @County

To see if I get the right records