Solved

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

Posted on 2012-03-26
9
354 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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