Stored procedure structure

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything

Stored procedure structure

Hi All,

 

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)

3 REPLIES 3
Teradata Employee

Re: Stored procedure structure

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?

 

dave

Re: Stored procedure structure

Hi Dave,
Thanks for your reply. The reason for using sp is because of huge data that leads to out of pool space issue. Each product just for a month have around 15 million records at line item level.
Ambassador

Re: Stored procedure structure

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.