Solved

MS SQL 2012 How to break a loop if a variable is empty or null

Posted on 2013-11-15
8
372 Views
Last Modified: 2013-11-18
Hi

I currently have some code which does the following:

1.  Runs through a temporary table based on an identity field
2. For each identity, stores a value to variable1
3. Stores a value from another table into variable2 based on variable1 being found in a particular column


What I need to do is break out of the loop if it doesn't find a match in the second table based on variable1.  I've tried various versions of null, zero and len, but can't get this to work.

I don't want to spend the day butchering the data to put it up here but I think the above should explain what I need.  I'm fairly new to SQL, so it's most likely just my lack of understanding it.

I'm awarding 500 points for a quick, working solution.

Thanks
Sarah
0
Comment
Question by:ScuzzyJo
[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
  • 4
  • 3
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39651169
Show us the code you have so far.
0
 

Author Comment

by:ScuzzyJo
ID: 39651179
Hi Jim

The main bit I can't get working is:

If LEN(@splitsSSN) = 0 BREAK;

Thanks
Sarah

WHILE @minID is NOT NULL 
BEGIN
    -- Get the various values, match records and perform calcs
	
	SELECT @remSSN = New_SSN FROM T_1213_Rem_SSN WHERE remId = @minID;
	SELECT @remAm = New_Rem_Amount FROM T_1213_Rem_SSN WHERE remId = @minID;
	SELECT @remDat = New_Rem_Date_Date FROM T_1213_Rem_SSN WHERE remId = @minID;
	SELECT @remOS = New_Rem_Amount FROM T_1213_Rem_SSN WHERE remId = @minID;
	WHILE @remOS <> 0 
	
	BEGIN
	SELECT @splitsSSN = student_support_number FROM T_1213_Splits_Final WHERE student_support_number = @remSSN;
	If LEN(@splitsSSN) = 0 BREAK;
	SELECT @conCatR = conCat FROM T_1213_Splits_Final WHERE student_support_number = @remSSN;
	SELECT @loanStart = payAmount FROM T_1213_Splits_Final WHERE student_support_number = @remSSN;
	SELECT @loanOS=@loanStart;
	If @remOS > @loanOS SELECT @remOS = @remOS - @loanOS;
	If @remOS > @loanOS SELECT @remApp = @loanOS;
	If @remOS > @loanOS SELECT @loanOS = 0;
	If @remOS = @loanOS SELECT @remOS = 0;
	If @remOS = @loanOS SELECT @remApp = @loanOS;
	If @remOS = @loanOS	SELECT @loanOS = 0;
	If @remOS < @loanOS SELECT @remOS = @remAm;
	If @remOS < @loanOS SELECT @remApp =0;
	If @remOS < @loanOS	SELECT @loanOS = @loanStart;
--	SELECT @loanOS = @loanStart - @remAm
	UPDATE T_1213_Splits_Final SET remApplied = @loanOS WHERE student_support_number = @remSSN;
	UPDATE T_1213_Splits_Final SET remAmount = @remAm FROM T_1213_Rem_SSN WHERE student_support_number = @remSSN;
	UPDATE T_1213_Splits_Final SET remDate = @remDat FROM T_1213_Rem_SSN WHERE student_support_number = @remSSN;
	UPDATE T_1213_Splits_Final SET remfromSSN =@minID FROM T_1213_Rem_SSN WHERE student_support_number = @remSSN;
	UPDATE T_1213_RemAds SET conCatRem = @conCatR FROM T_1213_Splits_Final WHERE SSN = @remSSN;
	UPDATE T_1213_RemAds SET remOutSt = @remOS FROM T_1213_Splits_Final WHERE SSN = @remSSN;
	UPDATE T_1213_RemAds SET remApplied = @remApp FROM T_1213_Splits_Final WHERE SSN = @remSSN;
	
	END

--    UPDATE T_1213_RemAds SET Rem_Amount = @remAm + 0.13 FROM T_1213_Rem_SSN WHERE SSN = @minID
     -- get the next record
	 DELETE FROM T_1213_Rem_SSN WHERE remId = @minID
    SELECT @minID = min(remId) FROM T_1213_Rem_SSN WHERE @minID < remId
END

Open in new window

0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39651278
Few things.

1. This select:

SELECT @splitsSSN = student_support_number FROM T_1213_Splits_Final WHERE student_support_number = @remSSN;

kind of deosn't make sense because you look in a table where a column equals a variable just to get that very value in another variable. It is basically the same with:

SELECT @splitsSSN=@remSSN

2. If condition:

If LEN(@splitsSSN) = 0 BREAK;

If @splitsSSN is a number LEN should not be uses as is for string values. You should use:

IF @splitsSSN IS NULL or @splitsSSN=0 BREAK:

3. The updates. You should update a table only once:

      UPDATE T_1213_Splits_Final SET
            remApplied = @loanOS,
            remAmount = @remAm,
            remDate = @remDat,
            remfromSSN =@minID
      WHERE student_support_number = @remSSN;

the other update doesn't make sense:

UPDATE T_1213_RemAds SET conCatRem = @conCatR FROM T_1213_Splits_Final WHERE SSN = @remSSN;

You updated T_1213_RemAds  table column conCatRem with variable @conCatR but then you go using FROM another table. This translates that the conCatRem column in the whole table will be updated as long there is a SSN in T_1213_Splits_Final table that equals @remSSN

This should be done differently because this way is wrong. You need to join teh 2 table if there is a relation between them.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:ScuzzyJo
ID: 39651339
Hi

I will certainly work through everything you've said but, first, I'd like to see it breaking out of the loop so that I'm comfortable with that.  I tried changing:

If LEN(@splitsSSN) = 0 BREAK;

to

IF @splitsSSN IS NULL or @splitsSSN=0 BREAK:

but it still carried onto the  next line where I wanted it to go to 35.  I think I'm missing something here!

Incidentally, I did try to get help on how to go about this whole bit but only got one reply asking for some sample data, which I posted on 12th, but I haven't had any replies to it at all since then :-(

Thanks
Sarah
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39651437
Oh, you used : (colon) which is wrong. I wonder how come you didn`t get any syntax errors.

Try this:

IF @splitsSSN IS NULL or @splitsSSN=0
      BREAK

New line for break and no punctuation.
0
 

Author Comment

by:ScuzzyJo
ID: 39651509
Hi

OK, that bit worked but it's now exiting the whole query when the IF statement isn't true.  It should go on and do the bits between the BEGIN and the END.  I'll have a look and see if I can see what the problem is, but may come back.

I'll also work through the rest of what you've said, but it'll take me a bit of time.

Thanks
Sarah
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39651583
0
 

Author Comment

by:ScuzzyJo
ID: 39656049
Hi

Quick update.  I still need to go through all this other than the bit I originally asked, which works now.  I've got the whole thing working (bar an ORDER BY question I've posted), even though it's not pretty.  I'm going to close the question and come back to you later as I don't think it's fair to keep you waiting for the points.

Thanks
Sarah
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 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