I've a list of products and month for which i need to fetch customer and sales details. I've created query using multiple joins to do this for a single product and month. But since there is a long list of products and month, i need to somehow extend the logic to work for multiple rows. Basically i need a loop that can solve this problem. It would be great if someone can guide me on how to do this using stored procedure. Please find below step wise break up of requirements which i just explained above.
I want to create a stored procedure that does the following:
Step 1: Get parameter inputs for the Stored procedure from the already existing table with columns Product_ID, Month
Step 2: Basis the above inputs as parameters, Get details such as customer info, sales, volume etc for the product for the month (I've the query to find all this using multiple join conditions)
Step 3: Insert the values from step 2 into a table name
Step 4: Repeat from Step 2 for the second row in Step 1 (input table)
Nothing about these requirements says you need to use a stored procedure to do it.
Why can you not run this as one big query storing all the results into your table in one pass?
You should check first, if you can improve your existing query.
Then ask your DBA for more spool.
If it's still running out of spool, you might consider splitting it into several steps, but not one query per product/month.