Stored Procs and temp tables 1

[Danster]

Hi all,

I have a stored procedure which returns 1 or 2 rows. No more than 2.

I have a table which I want to pass a value to the stored proc for every row in the table. My table only has about 30 or 40 rows.

Each time the stored proc runs with the current table.field value, I want the output in a temp table so eventually it will build up to contain all the stored proc results for each value in the table.

I am doing
INSERT INTO #MyTable EXEC MyStoredProc

which works excellently, thanks to a prior post on this issue - thanks vongrunt.

What I also need is the table.field value on the same row in #MyTable getting populated by the stored proc results.

What I already have is

<loop thru table>
@MyVariable = table.field
INSERT INTO #MyTable (@MyVariable, EXEC MyStoredProc)

but this is obviously the wrong syntax coz it aint working.

How can I do it?






BEGIN

 

[SQLDenis]

pass in the variable into the stored proc and return it in the select statement

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Danster]

No can do.

The stored procs are part of an app and written by the developers (not me).
I'm just running them for my own reporting requirements.

But I still need the table.field value on the same row in #MyTable as the stored proc results.

Other ideas?

cheers

 

[gmmastros]

You could create another temp table.

Each time through the loop...
1) Truncate the TemporaryTemp table.
2) Fill it with data from the sproc.
3) copy the data from the TemporaryTemp table with some other id in to the original temp table


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Danster]

George and others,

I can't get the syntax right to copy the data from the first temp table into the second with my field.

Ive tried
INSERT INTO #MyTable2 @MyVariable, SELECT * FROM #MyTable
but this doesn't work.

I've tried

INSERT INTO #MyTable2 VALUES (@MyVariable, #MyTable.Field1, #MyTable.Field2.....etc)
but that doesn't work either.

Other suggestions?

cheers

 

[gmmastros]

Give this a shot....

Insert Into #Mytable2 (IdField, Field1, field2)
Select @MyVariable, #MyTable.Field1, #MyTable.Field2
From #MyTable

If you try this and still can't get it to work, then post back, but with more code, and we'll be able to help better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Danster]

Yep, that works OK, but unfortunately, is is building up the second table with all the records from the first table as the program goes on.

So I get

VariableVal_1 field1 field2 field3 etc
VariableVal_2 field1 field2 field3
VariableVal_2 field1 field2 field3
VariableVal_3 field1 field2 field3
VariableVal_3 field1 field2 field3
VariableVal_3 field1 field2 field3

I'm so close I can almost smell success, if I can just get a single row for each @variable

I might try and DELETE FROM #MyTable so it's empty on each read of the stored proc. The data should've been populated into #MyTable2 already...

 

[gmmastros]

Yep. That's the advice I gave you on [red]30 Nov 05 17:40[/red]. I said, each time through the loop, you should first truncate the table. Truncate is another way of deleting all the data.

Ex.

Truncate Table #MyTable

Is 'effectively' the same as Delete From #MyTable

Truncate is faster because the data is not logged when you truncate.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Danster]

Ahhh. I see.
I was about to post a big WOOOOOHOOOOO coz its working when I do the DELETE FROM, but I'll change it to TRUNCATE

man, this has taken me so incredibly long!
I'm stoked!
Big fat star for you