CODEWITHSUNDEEP
sql-servert-sqlstored-procedurescursor
John_Snow
John_Snow

Reputation: 62

How to check if a Cursor Select query has records

I am new to Stored Procedures.I would like to have a cursor for selecting records from a table.If records is available insert into another.How to add a check for selected rows before insert.Thanks in advance.

Eg: 

DECLARE cursor_name CURSOR FOR 
        SELECT Id From tbl WHERE  where condition
OPEN cursor_name 

    FETCH NEXT FROM cursor_name INTO @id
    WHILE @@FETCH_STATUS = 0   
    BEGIN  
     Insert statement

Upvotes: 0

Views: 2877

Answers (1)

Rahul Richhariya
Rahul Richhariya

Reputation: 514

The variable @@FETCH_STATUS will return 0 only in case of success row is selected, based on your where condition. The code you provided in your question will be sufficient to take care of it. you can also refer below sample code. In this code i am inserting the row from table1 to table2 if the value of column1 is even and discarding odd rows -

-- CREATE Source table and add few records 
create table #Table1
(
Col1 int,
    col2 char(1)
)

insert into #Table1
select  1, 'A'
union all select  2, 'B'
union all select  3, 'C'
union all select  4, 'D'
union all select  5, 'E'
union all select  6, 'F'
union all select  7, 'G'
union all select  8, 'H'


--Create destination table 
create table #Table2
(
    Col1 int,
    col2 char(1)
)

declare @Col1 int,  @col2 char(1)  

-- below cursor will insert only evern rows from surce table to destination 
table 
DECLARE Cur1 CURSOR FOR  
SELECT   Col1,  col2
FROM #Table1
WHERE  col1%2 = 0   

OPEN Cur1   
FETCH NEXT FROM Cur1 INTO @Col1 ,@col2   

WHILE @@FETCH_STATUS = 0     -- this will return 0 only if a successful row is fatched 
BEGIN   
       insert into #Table2
       select @Col1 ,@col2       

       FETCH NEXT FROM Cur1 INTO @Col1 ,@col2     
END   

CLOSE Cur1   
DEALLOCATE Cur1

Upvotes: 1

Related Questions