Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Fetch Next Into Empty Check?

Posted on 2014-01-30
7
Medium Priority
?
1,016 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

618 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