CODEWITHSUNDEEP
sqlstored-procedures
FabianCook
FabianCook

Reputation: 20557

Loop over result in stored procedure

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

Upvotes: 1

Views: 390

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 2

cha
cha

Reputation: 10411

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

Upvotes: 2

Related Questions