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
COHFLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
- We need #Temp_1 to store record(s) returned by supplying  @account.
- 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
0
COHFLAuthor Commented:
Ok I guess I have everything in mind but did not put it in code =(

So I followed your suggestion but for some reason I don't think my #tblConsumption is been fill. here is the code I have so far
CREATE procedure [dbo].[usp_Consumption]
@AccountNumber as varchar(20)
As

Declare @MTUID int 
Declare @PortID Int
Declare @dtStart datetime
Declare @dtEnd datetime

CREATE TABLE #MTUPorts (
	[MTUID]	INT, 
	[PORT]	INT )
	
CREATE TABLE #tblConsumption (
	[ID]				INT IDENTITY(1,1) PRIMARY KEY,
	[ReadingDate]		DateTime,
	[RawConsumption]	Decimal(20,6),
	[Consumption]		Decimal(20,6),
	scalar				REAL,
	Units				INT,
	Utility				NVARCHAR(20),
	AccountNum			NVARCHAR(20),
	SerialNumber		NVARCHAR(20)
)
SET @dtStart = CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101)
SET @dtEnd = DateAdd(dd, -30, @dtStart)

INSERT INTO #MTUPorts
SELECT [MTU ID], [Port Number] FROM [Account MTU Meter Xref] 
where AccountNum =RIGHT(REPLICATE('0',9) + CAST(@AccountNumber AS VARCHAR(9)),9) and [State] = 1

declare curM cursor for 
SELECT * FROM #MTUPorts
OPEN curM
Fetch next from curM Into @MTUID, @PortID
while @@FETCH_STATUS = 0 begin

insert into #tblConsumption
EXEC starNG_CalcConsumption @MTUID, 
	@port =@PortID, 
	@dateStart = @dtStart, 
	@dateEnd =@dtEnd
	
Fetch next from curM Into @MTUID, @PortID	
	
end
close curM
deallocate curM

SELECT * FROM #tblConsumption

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
COHFLAuthor Commented:
Although the guidance was there, there was not syntax to follow.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.