Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2008
    Posts
    35

    Unanswered: Cursors and Nulls

    Hi

    When I am use a Cursor to fetch data (lets say 5 pieces of data in a row),
    it would not assign any variables if any one of them returns null.

    I need to use CURSOR as well as store NULLs.

    Please would anyone provide me with any info on how to accomplish this.


    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @i		INT
    
    DECLARE zNullDemo CURSOR FOR SELECT 1 AS i
       UNION SELECT 2
       UNION SELECT NULL
       UNION SELECT 4
       UNION SELECT 5
       
    OPEN zNullDemo
    FETCH zNullDemo INTO @i
    
    WHILE 0 = @@FETCH_STATUS
       BEGIN
          SELECT @i
          FETCH zNullDemo INTO @i
       END
    
    CLOSE zNullDemo
    DEALLOCATE zNullDemo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2008
    Posts
    35
    Hi Thanks for the reply but I am facing a different issue, here let me give an example:




    Code:
    DECLARE MY_Cursor Cursor FOR	
    	Select  * -- supposedly it has five fields	
    	from tableName
    Open My_Cursor
    Fetch NEXT FROM MY_Cursor INTO @var1, @var2, @var3, @var4, @var5 
    
    While (@@FETCH_STATUS = 0)--Fetch is successful
    begin
    	insert into anotherTable
    	values (@var1, @var2, @var3, @var4, @var5)
    
    	IF (@@FETCH_STATUS = -1) BREAK 
    end 
    close my_cursor 
    DEALLOCATE MY_CURSOR


    In such a scenario if any one of the field contains a null then the varialbes do not change and retain its earlier fetched row value.

    Please can any help.

    Thanks

  4. #4
    Join Date
    Jun 2009
    Posts
    66
    INSERT INTO anotherTable
    (var1, var2, var3, var4, var5)
    SELECT var1, var2, var3, var4, var5 FROM tablename

    will perform much better

  5. #5
    Join Date
    Jun 2008
    Posts
    35
    Thanks SQLguru, I'm aware of that but I need a way around this problem with cursors.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Replace the * with a list of columns.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2008
    Posts
    35
    sorry but it still doesn not work

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your cursor construct appears wrong - you're missing a fetch?

    Try this - note the explicit declaration of each column in both the select and insert lists
    Code:
    DECLARE @var1 char(1)
          , @var2 datetime
          , @var3 bit
          , @var4 int
          , @var5 varchar(40)
    
    DECLARE the_cursor CURSOR FOR	
      SELECT col1
           , col2
           , col3
           , col4
           , col5
    	FROM   your_table
    
    
    OPEN the_cursor
    FETCH NEXT FROM the_cursor INTO @var1, @var2, @var3, @var4, @var5 
    
    WHILE @@Fetch_Status = 0
      BEGIN
        INSERT INTO another_table (col1, col2, col3, col4, col5)
          VALUES (@var1, @var2, @var3, @var4, @var5)
      
        FETCH NEXT FROM the_cursor INTO @var1, @var2, @var3, @var4, @var5
      END
    
    CLOSE the_cursor 
    DEALLOCATE the_cursor
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2008
    Posts
    35
    Sorry but thanks gvee it was an error in typing in the code in the forum but I have that line in my actual code os its like below:
    Code:
    While (@@FETCH_STATUS = 0)--Fetch is successful
    begin
    	insert into anotherTable
    	values (@var1, @var2, @var3, @var4, @var5)
    
    	Fetch NEXT FROM MY_Cursor INTO @var1, @var2, @var3, @var4, @var5 
    	IF (@@FETCH_STATUS = -1) BREAK 
    end
    I found this link and now I know why NULLs are not fetched, please check the below link everyone:

    Differences between SET and SELECT in SQL Server : Narayana Vyas Kondreddi's home page

    But the problem is I need to use cursor and fetch NULL does any one know how to accomplish that.


    Thanks

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest that you see this example that shows how to fetch a NULL value into a variable using a cursor.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try looking up null indicators.
    Dave

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    WHY do you HAVE to use a Cursor?

    99.9999% of the time you don't need to
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I can not replicate the problem, either.
    Code:
    create table #temp 
    (col1 int,
     col2 varchar(10))
    
    create table #temp2 
    (col1 int,
     col2 varchar(10))
    
    insert into #temp 
    select 1, 'hello' union all
    select 2, 'hi' union all
    select 3, 'morning' union all
    select 4, null union all
    select 5, 'good bye'
    
    
    declare curses cursor for select * from #temp
    declare @var1 int
    declare @var2 varchar(10)
    
    open curses
    
    fetch next from curses into @var1, @var2
    
    while @@fetch_status = 0
      begin
    	insert into #temp2 values (@var1, @var2)
    	fetch next from curses into @var1, @var2
      end
    close curses
    deallocate curses
    
    select * from #temp
    select * from #temp2
    
    drop table #temp
    drop table #temp2
    The row with the NULL is processed exactly like any other. And why is the cursor necessary?

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The "Problem" is using a cursor for no good reason
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •