[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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.
0
kojak33
Asked:
kojak33
  • 3
  • 3
  • 2
1 Solution
 
rafranciscoCommented:
Can you please post your stored procedure to give us a better idea of what you are trying to do.
0
 
kojak33Author Commented:
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

0
 
Brian CroweCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rafranciscoCommented:
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.
0
 
Brian CroweCommented:
If you show us your actual code we promise not to share it with anyone....we'll call it querier-expert confidentiality ;-)
0
 
kojak33Author Commented:
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
0
 
kojak33Author Commented:
the query in the nested cursor is just SELECT user_id from TableD WHERE somecondition (will return 1 or more user_id's)
0
 
rafranciscoCommented:
If you will just insert into a table a given list of values only if it doesn't exist, you can do this to avoid the cursor

INSERT INTO TableC (Your Columns...)
SELECT YourColumns... FROM TableD
WHERE NOT EXISTS (SELECT 'X' FROM TableC
                               WHERE TableC.UniqueID = TableD.UniqueID)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now