2

I have a query as followed. I would get multiple MethodID from the select. Do I use while loop or another way to read each MethodID retrieved from the select to compromise the IF ELSE? (@PID would be a input parameter) How do I do it?

SELECT DISTINCT MethodID FROM Table
WHERE PID = @PID

IF @MethodID = 10
   EXEC sp1
ELSE IF @MethodID = 20
   EXEC sp2
Improve this question

2 Answers 2

Reset to default
1

If the query returns a set of MethodIDs and the set may contain more than 1, if all you need to know is if the set contains 10, then use an IN or EXISTS clause:

IF (EXISTS (SELECT 1 FROM Table WHERE PID = @PID AND MethodID = 10))
    EXEC sp1

IF (EXISTS (SELECT 1 FROM Table WHERE PID = @PID AND MethodID = 20))
    EXEC sp2

If there are lots of cases and this query doesn't perform well, consider loading the results into a temp table.

This could also be done with IN clauses, but EXISTS is much easier in this case and may perform better.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You think it's only one execution? The question is unclear in that regard.
As near as I can tell, I think so. The OP used DISTINCT, so 10 and 20 would each appear at most once. They didn't look for any values other than 10 or 20. I agree, the question is unclear.
Right my question is unclear. I would get more values from the select and more ELSE IF. Not only 10 and 20. So in this case cursor would be a better option?
Can you clarify the question a bit? I am still struggling to see why a cursor solution is needed, but since everybody else disagrees with me, there must be something I am not seeing.
after review the process again, I realized the MethodID will be an input parameter as PID, so it will only return 1 record, so I will use IF EXISTS method as Brandon suggested instead of using cursor (however cursor is another option to use). Thank you both!
0

Write a loop iterating over the result set of a cursor. This is a good use case for cursors because you need to perform a side-effect (a procedure call) per iteration. You need a loop anyway and a cursor is convenient to use here.

Improve this answer

9 Comments

I usually recommend against cursors unless there is no alternative.
@Brandon nothing wrong with using them if you need to execute code per-row anyway. What's the disadvantage? There's no set-based solution possible here.
Yes there is. They want to know if there is a 10 or a 20 in a set of integers.
And they want to execute a prov per row.
Your use of DISTINCT results in only returning each number at most once. The procedure call does not include any parameters from the original query, so the only question is "does this set contain a certain number", which is much simpler than a row-by-row proc call in a cursor loop.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.