How do I create a looping stored procedure based on column input from another table?

Question

Hi all, 

Right now I am working on doing some calculations for a report. The T-SQL for this is only 106 lines long - so it's not a huge process at all. My question is this:

I'm doing this calculation for 8 distinct categories. Is there a way to write a stored procedure where I can put the names of those 8 categories into a table - and then feed them into a cursor or variable (or use FETCH) to set them equal to a parameter in a stored procedure and have that parameter loop through the 106 lines of code? (and put all 106 lines in the stored procedure)

Sort of like a table-based while loop...

Thanks!

Answer 1

You can invoke your stored procedure with a parameter in a cursor based loop.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Instead of stored procedure looping, just use dynamic SQL:

http://www.sqlusa.com/bestpractices/dynamicsql/

For additional assistance, post your code.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 3

Have you tried a table valued parameter ?

---

Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/