execute a stored procedure based on a dynamic value
Hi there,
Im going to try to indicate my process yhe best that I can maybe you can provide me some guidance on whats yhe best practice to do what I want.
I have a variable lets call it @account with that variable I get some key values for my result set.
Select id, port from mytable where accountnum = @account
The result from this query might yield one row or many rows which is totally fine. For each row in that result set I need to execute a stored procedure.
Exec procedured usp_history
The values of this execution I need to keep adding to a tmp table that at the end will return all the data I need.
How can accomplish this effectively that it is not costing performance?
Thanks in advance for your update
Im going to try to indicate my process yhe best that I can maybe you can provide me some guidance on whats yhe best practice to do what I want.
I have a variable lets call it @account with that variable I get some key values for my result set.
Select id, port from mytable where accountnum = @account
The result from this query might yield one row or many rows which is totally fine. For each row in that result set I need to execute a stored procedure.
Exec procedured usp_history
The values of this execution I need to keep adding to a tmp table that at the end will return all the data I need.
How can accomplish this effectively that it is not costing performance?
Thanks in advance for your update
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is impossible for me to test your code without some sample data I asked for. Because i can not test your code, you need to play detective like.
Step 1 - We need #Temp_1 to store record(s) returned by supplying @account.
Question 1: Did #Temp_1 updated with the intended results?
If Question 1 yes, then continue with
Step 2 - Declare and set a cursor based on this #Temp_1
Question 2: Does cursor cycles properly? You can add some print or select to check this.
If Question 2 yes, then continue with
Setep 3 - Cycle through the cursor object to run usp_history.
Question 3: Does usp_history return data as expected? You can add some print or select to check this.
If Question 3 yes, then continue with
Step 4 - Store the values returned into #Temp_2
Question 4: does #Temp_2 get populated? You can add some print or select to check this.
If Question 4 yes, then the mission is accomplished. If not in any of the steps above then add comment with focus where the problem is.
As you may agree I cannot test the above steps because I do not have access to your data.
Mike
Step 1 - We need #Temp_1 to store record(s) returned by supplying @account.
Question 1: Did #Temp_1 updated with the intended results?
If Question 1 yes, then continue with
Step 2 - Declare and set a cursor based on this #Temp_1
Question 2: Does cursor cycles properly? You can add some print or select to check this.
If Question 2 yes, then continue with
Setep 3 - Cycle through the cursor object to run usp_history.
Question 3: Does usp_history return data as expected? You can add some print or select to check this.
If Question 3 yes, then continue with
Step 4 - Store the values returned into #Temp_2
Question 4: does #Temp_2 get populated? You can add some print or select to check this.
If Question 4 yes, then the mission is accomplished. If not in any of the steps above then add comment with focus where the problem is.
As you may agree I cannot test the above steps because I do not have access to your data.
Mike
ASKER
Although the guidance was there, there was not syntax to follow.
- Declare and set a cursor based on this #Temp_1
- Cycle through the cursor object to run usp_history.
- Store the values returned into #Temp_2
Basically you have 4 question squeezed in one. After a solution like this (or any other) consider to post another question as to how to optimize it (if it was too slow).
If you one to post 5 different questions as stated above or you want all to be handled here, you need to supply some sample data, field names, code in progress so we can refine and test it.
Mike