Solved

SQL - insert result of stored procedure and the parameter pass to the stored procedure

Posted on 2012-03-26
9
358 Views
Last Modified: 2012-04-05
I  would like to store the stored procedure along with the parameters.  How do I do that?

I know I have to create a temp table.
I can use a insert statement as follow to insert result into a tempable

insert into #temptable EXEC MyStoreProcedure Parm1 Parm2

I am hoping to get the result temptable in the following format:
Parm1, Parm2,  StoredProcedureResultColum1, StoredProcedureResultColum2, ...

Any help will be appreciated.
0
Comment
Question by:tommym121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 9

Assisted Solution

by:OCDan
OCDan earned 250 total points
ID: 37767669
I'm not too clear on what you want to do, but your final select/insert in your stored procedure should look kind of like this:

select
@param1 'Param1', @param2 'param2', spcolumn1, spcolumn2....
INTO #temp
from wherever

select * from #temp

p.s. to be clear @param1, @param2 should be whatever you defined your parameters as in the opening clause of your SP.

e.g.
create procedure sp_test
(
@param1 int,
@param2 int
)
........
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37767860
Note that you must create the temp table prior to EXECUTEing the stored proc.  

Then you can INSERT into the temp table throughout the proc as required.

And read the temp table upon RETURN from the procedure call.

[If you create the temp table in the stored proc itself, it will automatically be deallocated when EXITing the stored proc.]
0
 

Author Comment

by:tommym121
ID: 37769383
I got so far I need to create a temp table prior to execute the stored proc.
I know how to insert the result of the executed store proc into the temp table, however, I would also like to insert the parms used in the stored proc into every row of the resulting table.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 9

Expert Comment

by:OCDan
ID: 37769938
tommym121 if you look at my answer you will see it inserts the params used into EVERY ROW as you wish.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 37771591
>> if you look at my answer you will see it inserts the params used into EVERY ROW as you wish. <<

Actually, not quite.  You create the temp table in the stored proc -- which means it will be destroyed when the stored proc is exited.


As I noted earlier, you must create the temp table *before* executing the called proc.  That's a bit of a pain but there's no easy way around it.


When you INSERT to that temp table from the stored proc, you can include @param_values just as would any other column in the result set.

For example:


CREATE TABLE #results ( param1 int, param2 varchar(30), param3 datetime,
    output1 int, output2 decimal(11, 3) ) ...

EXEC proc_name @param1, @param2, @param3


CREATE PROCEDURE proc_name (
    @param1 ...,    
    @param2 ...,
    @param3 ...
AS
...
INSERT INTO #results (param1, param2, param3, output1, output2, ...)
SELECT @param1, @param2, @param3, tn.col1, tn.col2 * @param1, ...
FROM table_name tn
...
0
 
LVL 9

Assisted Solution

by:OCDan
OCDan earned 250 total points
ID: 37771743
Scott, it depends entirely on how he wants it to work. If he wants a table that he can access outside of the SP then obviously the temp table needs to be included outside of the storedprocedure itself.

If this was the case I would question the point of using a temp table.

The way did it a simple select * from temp at the bottom of the SP will return the required recordset.

Either way I think between what both of us have said he should have his answer, regardless of which way he needs it to work :)
0
 

Author Comment

by:tommym121
ID: 37774362
In my case,  I can only use MyStoredProcedure to generate result.  I do not have access to the code of MyStoredProcedure.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 37776875
Hmm ... I think the best try is something like this:


CREATE TABLE #result ( parm1 ..., parm2 ..., output1 ..., output2 ...)

INSERT INTO #result( output1, output2, ...)
EXEC MyStoreProcedure Parm1 Parm2

UPDATE #result
SET parm1 = parm1_value,
      parm2 = parm2_value

SELECT *
FROM #result
0
 

Author Closing Comment

by:tommym121
ID: 37815014
Thanks
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question