Cursor in While loop

My Cursor and Output

SET NOCOUNT ON
DECLARE @vendor_id int, @vendor_name nvarchar(50)
DECLARE @subvendor_id int, @subvendor_name nvarchar(50)   
PRINT '-------- Vendor Products Report --------'
DECLARE vend_cursor CURSOR FOR SELECT * FROM MYSEQ

OPEN vend_cursor
FETCH NEXT FROM vend_cursor 
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    -- Declare an inner cursor based   
    -- on vendor_id from the outer cursor.
    DECLARE product_cursor CURSOR FOR  SELECT * FROM MYSEQ

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @subvendor_id,@subvendor_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF(@subvendor_id >3)
            SELECT * FROM MYSEQ WHERE SQLID =@subvendor_id
            FETCH NEXT FROM product_cursor INTO @subvendor_id,@subvendor_name
    END

    CLOSE product_cursor
    DEALLOCATE product_cursor
    -- Get the next vendor.
    FETCH NEXT FROM vend_cursor INTO @vendor_id, @vendor_name
END 
CLOSE vend_cursor
DEALLOCATE vend_cursor

Question

I am getting answer in multiple row values. I need as like collection of rows like this

4 text4
5 text5

(Also should not contain duplicate records)

Edit: This is the sample query only. I need to do lot of conditions. THat is why I wrote like this.

Edit Cursor

I need the overlapping records in date...

开发者_如何学Python

RowId, CheckIn, CheckOut
1      10 AM    2 PM
2      10.30 AM  11.30 AM
3     8 AM     9 AM

I want to select only the overlapping records and also how many overlapping pairs... that is why I go for Multiple cursor

---

Forgetting the use of cursors in SQL for now...

In any language, why would you declare an inner loop to have the same iteration as the outer loop? In this case both cursors are based on SELECT * FROM MYSEQ.

---

The @@FETCH_STATUS is a global variable available to all cursors on a connection, so when the nested cursor is finished it will set the global variable to -1 (to indicate end of rows) the outer loop will also terminate. see https://msdn.microsoft.com/en-GB/library/ms187308.aspx

---

Three things occur to me.

I presume this is some test you are working on. But you have defined both cursors to execute exactly the same query. So that is one reason why you might be getting duplicate rows. Certainly, the lack of clear business logic in your question makes it hard for us to understand what you are trying to achieve.

The second is, you have a nested loop, looping through the records of one table within the records of another table. But you have neglected to join the rows selected by the inner cursor with the current row of the outer cursor. So each row in the vend_cursor will fetch the entirely the same set of rows within the product_cursor.

Lastly, the product_cursor is not sorted or filtered by the distinct keyword. Given the two previous points this may not matter. I just mention it for the sake of completeness.

---

Cusrors in nested loops works fine now, see https://learn.microsoft.com/ru-ru/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15

Just use DEALLOCATE statement after CLOSE

SET NOCOUNT ON;  
  
DECLARE @vendor_id INT, @vendor_name NVARCHAR(50),  
    @message VARCHAR(80), @product NVARCHAR(50);  
  
PRINT '-------- Vendor Products Report --------';  
  
DECLARE vendor_cursor CURSOR FOR   
SELECT VendorID, Name  
FROM Purchasing.Vendor  
WHERE PreferredVendorStatus = 1  
ORDER BY VendorID;  
  
OPEN vendor_cursor  
  
FETCH NEXT FROM vendor_cursor   
INTO @vendor_id, @vendor_name  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ' '  
    SELECT @message = '----- Products From Vendor: ' +   
        @vendor_name  
  
    PRINT @message  
  
    -- Declare an inner cursor based     
    -- on vendor_id from the outer cursor.  
  
    DECLARE product_cursor CURSOR FOR   
    SELECT v.Name  
    FROM Purchasing.ProductVendor pv, Production.Product v  
    WHERE pv.ProductID = v.ProductID AND  
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor  
  
    OPEN product_cursor  
    FETCH NEXT FROM product_cursor INTO @product  
  
    IF @@FETCH_STATUS <> 0   
        PRINT '         <<None>>'       
  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
  
        SELECT @message = '         ' + @product  
        PRINT @message  
        FETCH NEXT FROM product_cursor INTO @product  
        END  
  
    CLOSE product_cursor  
    DEALLOCATE product_cursor  
        -- Get the next vendor.  
    FETCH NEXT FROM vendor_cursor   
    INTO @vendor_id, @vendor_name  
END   
CLOSE vendor_cursor;  
DEALLOCATE vendor_cursor;