• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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

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
ScuzzyJo
Asked:
ScuzzyJo
  • 4
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Show us the code you have so far.
0
 
ScuzzyJoAuthor Commented:
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
 
ZberteocCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ScuzzyJoAuthor Commented:
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
 
ZberteocCommented:
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
 
ScuzzyJoAuthor Commented:
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
 
ScuzzyJoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now