Solved

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

Posted on 2012-03-26
9
352 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

911 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

22 Experts available now in Live!

Get 1:1 Help Now