?
Solved

SQL Fetch Next Into Empty Check?

Posted on 2014-01-30
7
Medium Priority
?
977 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 1200 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

801 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