Solved

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

Posted on 2012-03-26
9
349 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
  • 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:ScottPletcher
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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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:
ScottPletcher 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 and SQL 2014 in memory database 11 32
SQl query 19 14
Pivot not using aggregate yield error 3 21
sql query Help 12 34
In this article I will describe the Copy Database Wizard 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now