Loop over result in stored procedure

Question

How is it possible to loop over a set of selected results in a stored procedure and take out the ones that match a criteria.

If it shouldn't need a loop or something of the sort how would I achieve this?

So lets say we have a select statement like so

SELECT DefaultID, Weight, Length, Height, Width FROM tblDefault 
WHERE CustomerID=@CustomerID AND  DeleteDateUTC is null

But how can I get only the rows that also match the criteria

IF SELECT 1 FROM tblOther WHERE CustomerID=@CustomerID AND CustomerUserID=CustomerUserID 
AND DefaultID=@DefaultID returns 1 row 

OR SELECT 1 FROM tblOther WHERE CustomerID=@CustomerID AND DefaultID=@DefaultID returns nothing

In short I am wanting to select rows in tblDefault where the defaultID is in tblOther with the CustomerUserID or the defaultID isn't in tblOther

You rarely need to loop with SQL. Some whizz will come along with the correct proc, but the direction will probably be to use [group by with count()](http://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php), and use that either in a join or as a subquery with the first statement you have. — ian, Jan 24 '13 at 02:17
Check the second paragraph :P. I didn't think it would need one and there would be some smart way of doing it. — FabianCook, Jan 24 '13 at 02:20
"If it shouldn't need a loop or something of the sort how would I achieve this?" — FabianCook, Jan 24 '13 at 02:22
Ah, *your* second paragraph :) Oh well, then I was just backing you up! — ian, Jan 24 '13 at 02:22
Thanks. :). Maybe some subselects will help. But how can I check the amount of rows returned? — FabianCook, Jan 24 '13 at 02:23
Remembering that we need to use the defaultID returned from the initial query — FabianCook, Jan 24 '13 at 02:26
[Using info from this question](http://stackoverflow.com/questions/3884733/sql-subquery-with-count-help) I get `SELECT DefaultID WHERE CustomerID=@CustomerID AND CustomerUserID=CustomerUserID AND DefaultID=@DefaultID HAVING count(*) > 0` as your subselect/join. Something like that. — ian, Jan 24 '13 at 02:33
But how can I use defaultID in the subquery when I am getting it in the main query. — FabianCook, Jan 24 '13 at 02:34

cha · Accepted Answer · 2013-01-24T02:52:10.147

2

To answer your question check EXISTS (if I correctly understood your question - otherwise please edit your question to make more sense)

SELECT d.DefaultID, d.Weight, d.Length, d.Height, d.Width 
FROM tblDefault d
WHERE CustomerID=@CustomerID AND  DeleteDateUTC is null
AND (EXISTS(SELECT 1 FROM tblOther WHERE CustomerID=@CustomerID AND CustomerUserID=@CustomerUserID 
     AND DefaultID=d.DefaultID) 
OR NOT EXISTS(SELECT 1 FROM tblOther WHERE CustomerID=@CustomerID AND DefaultID=d.DefaultID))

edited Jan 24 '13 at 02:52

answered Jan 24 '13 at 02:37

cha

10,301
1
18
26

Check the edit. Last line. I think I may have cleared up what I wanted. – FabianCook Jan 24 '13 at 02:41
@cha good idea, no need for count as exists will work for both cases, +1 – ian Jan 24 '13 at 02:53
But `DefaultID` is not a passed parameter, it is one of the columns we select in the main query. – FabianCook Jan 24 '13 at 02:54
Or do I just not understand sql? xD. Which is more likely the case – FabianCook Jan 24 '13 at 02:55

score 2 · Answer 2 · answered Jan 24 '13 at 02:58

I think this is the query that meets your conditions:

SELECT DefaultID, Weight, Length, Height, Width
FROM tblDefault td
WHERE CustomerID=@CustomerID AND  DeleteDateUTC is null and
     ( (SELECT count(*)
        FROM tblOther t
        WHERE CustomerID=@CustomerID AND t.CustomerUserID=td.CustomerUserID AND DefaultID=@DefaultID
       ) = 1 or
       not exists (SELECT 1 FROM tblOther WHERE CustomerID=@CustomerID AND DefaultID=@DefaultID)
    )

Loop over result in stored procedure

2 Answers2