[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2012-03-26
9
Medium Priority
?
368 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 1000 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 70

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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 1000 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
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.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

649 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