Solved

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

Posted on 2013-11-15
8
366 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 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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