Solved

SQL Fetch Next Into Empty Check?

Posted on 2014-01-30
7
943 Views
Last Modified: 2014-02-01
I'm using MSSQL 2008.
How do I check if the @State variable is null.
My code below doesn't work reliably.

Open @Cursor2
                        
Fetch Next From @Cursor2 Into @State

if @State is null
      Fetch Next From @Cursor2 Into @State

Begin
 ....
End
0
Comment
Question by:WorknHardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39823030
As I understood correctly, you are facing a problem that you have some rows where the column that you bind to @State variable is NULL. You want to skip these rows. Your code will just skip one row, and if the row that follows the skipped row also contains a NULL value, it will not be skipped. You have not showed us the whole code, but assuming that you are looping through the cursor using a WHILE loop, you can use CONTINUE, like this:
DECLARE @Cursor2 CURSOR FOR
SELECT [StateColumn] FROM MyTable;
OPEN @Cursor2;
Fetch Next From @Cursor2 Into @State;
WHILE @@FETCH_STATUS = 0
   BEGIN
      Fetch Next From @Cursor2 Into @State;
      IF @State IS NULL
           CONTINUE
   END;
CLOSE @Cursor2;
DEALLOCATE @Cursor2;
GO 

Open in new window

0
 

Author Comment

by:WorknHardr
ID: 39823052
I want to skip any row having nulls. Also, I need to check for more than one @Var

Open @Cursor2
                       
Fetch Next From @Cursor2 Into @State, @Name, @Price, @Date

if @State is null
      Fetch Next From @Cursor2 Into @State, @Name, @Price, @Date

While @@FETCH_STATUS = 0            
    Begin
           ....
          Fetch Next From @Cursor2 Into @State, @Name, @Price, @Date
     End

Close @Cursor2
Deallocate @Cursor2
0
 
LVL 25

Accepted Solution

by:
chaau earned 300 total points
ID: 39823085
In this case, put the check for NULLS inside your WHILE loop:
Open @Cursor2
Fetch Next From @Cursor2 Into @State, @Name, @Price, @Date
While @@FETCH_STATUS = 0            
    Begin
          if @State is null OR @Name IS NULL OR @Price IS NULL etc...
          BEGIN
              Fetch Next From @Cursor2 Into @State, @Name, @Price, @Date
              CONTINUE
          END
           ....
          Fetch Next From @Cursor2 Into @State, @Name, @Price, @Date
    End
Close @Cursor2
Deallocate @Cursor2 

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 15

Expert Comment

by:JimFive
ID: 39824173
Why don't you change your CURSOR Query to eliminate rows that have NULL in them?
0
 

Author Comment

by:WorknHardr
ID: 39824919
It's a troubleshooting thing, not permanent, good call
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39825554
Try avoiding cursor if possible. If you can explain your question, we can try solving it without cursor.
0
 

Author Closing Comment

by:WorknHardr
ID: 39826468
Thx
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question