asked on

nested loops in stored procedure

I have a sproc where I have a select statement creating a cursor. Then within that cursor loop, I need a nested loop. Everything I've read seems to say to avoid the nested cursors, but I can't find a better way to do what needs to be done.

My nested cursor needs to loop thru the records of a SELECT statement and perform an Insert/Update based on those records

Thanks for the help.

rafrancisco

Can you please post your stored procedure to give us a better idea of what you are trying to do.

kojak33

ASKER

Using the following select statement as a cursor:

SELECT cola, colb, colc From tableA

WHILE @@FETCH_STATUS = 0
BEGIN
run some checks on tableB, if NOT EXISTS Then Insert new record into TableB based on info from tableA

Here is my nested loop:
create loop based on SELECT colg, FROM tableC
perform similar operations as above.
END

Brian Crowe

What kind of checks are you running. It may be possible to do this without a CURSOR but you'll have to be more specific.

rafrancisco

If you are just checking for existence of records in TableB based on info on TableA, you won't be needing a cursor for that.

Brian Crowe

If you show us your actual code we promise not to share it with anyone....we'll call it querier-expert confidentiality ;-)

kojak33

ASKER

DECLARE SalesMan CURSOR FOR
      SELECT * FROM TableA

OPEN SalesMan
FETCH NEXT FROM SalesMan INTO @unit_num, @subject, @message, @user_id
WHILE @@FETCH_STATUS = 0
      BEGIN

      IF EXISTS(SELECT MsgID FROM TableB WHERE Subject = @subject AND datediff("d",dateupdated,getdate()) > 30 )
            UPDATE TableB SET Subject = @subject, Message = @message, dateupdated = getdate()
            SET @ident = (SELECT MsgID FROM TableB WHERE Subject = @subject AND datediff("d",dateupdated,getdate()) > 30)

            IF NOT EXISTS (SELECT MsgID FROM TableD WHERE MsgID = @ident AND MsgTo = @User_ID)
                  INSERT INTO TableD (MsgID, MsgTo, AllowHaveRead, HaveRead) VALUES (@ident, @User_ID, 1, 0)
            ELSE
                  IF NOT EXISTS (SELECT MsgID FROM TableD WHERE MsgId = @ident AND MsgTo = @User_ID AND DateRead = null)
                        UPDATE TableD SET AllowHaveRead = 1,HaveRead=0 WHERE MsgId = @ident AND MsgTo = @User_ID

      IF NOT EXISTS(SELECT MsgID FROM TableB WHERE Subject = @subject)            INSERT INTO TableB (CreatedBy,Subject,Message,DateUpdated) VALUES ('Some Value',@subject,@message,getdate())
            SET @ident = @@IDENTITY

            IF NOT EXISTS (SELECT MsgID FROM TableD WHERE MsgID = @ident AND MsgTo = @User_ID)
                  INSERT INTO TableD (MsgID, MsgTo, AllowHaveRead, HaveRead) VALUES (@ident,@User_ID,1,0)

      --Run Nested Cursor Here, doing inserts on TableD, based on query from TableC

      FETCH NEXT FROM SalesMan INTO @unit_num, @subject, @message, @user_id
      END

kojak33

ASKER

the query in the nested cursor is just SELECT user_id from TableD WHERE somecondition (will return 1 or more user_id's)

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial