Solved

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

Posted on 2013-11-15
8
359 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
  • 4
  • 3
8 Comments
 
LVL 65

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 26

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
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.

 

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 26

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 26

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

828 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