Solved

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

Posted on 2013-11-15
8
346 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
Comment Utility
Show us the code you have so far.
0
 

Author Comment

by:ScuzzyJo
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ScuzzyJo
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:ScuzzyJo
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now