Solved

SQL Fetch Next Into Empty Check?

Posted on 2014-01-30
7
792 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
7 Comments
 
LVL 24

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 24

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 40

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now