Solved

SQL Fetch Next Into Empty Check?

Posted on 2014-01-30
7
858 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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