[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

running EXEC results into a table

i'm trying to pipe the results of sp being called from another sp. i don't want to create the table (like the select * into...) and the variable first.

this works:

EXEC wk_GetPolicyData @SSN, @POLNUM, @EFFDATE

but not when i INTO whatever. thanx~!
0
ironpen45
Asked:
ironpen45
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>i'm trying to pipe the results of sp being called from another sp.
>i don't want to create the table (like the select * into...) and the variable first.
you cannot. you need a table first, OR change the procedure into a stored function.
0
 
rw3adminCommented:
if you dont need to create table with select * into... (which you cant with an exec proc) you need to create a table first... temp or actual is your choice...

suppose EXEC wk_GetPolicyData @SSN, @POLNUM, @EFFDATE returns three columns Col1 int, Col2 Varchar(100) and Col3 DateTime you can do

create table #T(Col1 int, Col2 Varchar(100) ,Col3 DateTime )
insert #T
EXEC wk_GetPolicyData @SSN, @POLNUM, @EFFDATE
0
 
ironpen45Author Commented:
if the destination table had different fields, how do i pipe the VALUES from the executed sp into the table? i tried the OUTPUT option but doesn't work
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
rw3adminCommented:
you should create the destination table as the same data structure returned from first one, the only other way I can think of is create destination table with Varchar(8000) for all columns ( I know its a overkill but just to be sure) and do a insert same way and then read data back from destination table while converting to appropriate datatype... but even then you will have to know how many columns are returning from your wk_GetPolicyData proc.

one more thing and angelIII will tell at me for that and you already said you dont want to use select into, is you can write a select into statement in wk_GetPolicyData proc before your final select, ....select into a physical table and then that table is available to you... just make sure you write drop physical table command in your wk_GetPolicyData proc before you do select into...

0
 
imran_fastCommented:
hi ironpen45,
You have to define the destination database as rw3admin suggest in the first post. it means one destination table per procedure.
or
You can define global destination table that will have all the fields from all possible sp's you have and then specify the field names when inserting from sp

insert into table(col1, col100, col101)
exec sp_idjfi @v1, @ip1
0
 
ironpen45Author Commented:
now if i wanted to UPDATE an existing table, how can i pipe the results for the update of, say Carrier_Risk_Number (from tblTEMP_tblIDIIncreaseUpload) like the following:

WHILE @@FETCH_STATUS = 0
BEGIN
      UPDATE tblTEMP_tblIDIIncreaseUpload SET PreUpdateStatus=Carrier_Risk_Number
          EXEC wk_GetPolicyData @soc_sec_no, @policy_no, @effective_date
    FETCH NEXT FROM curIterate INTO @soc_sec_no, @policy_no, @effective_date
END
0
 
rw3adminCommented:
is Carrier_Risk_Number field coming from wk_GetPolicyData?
then assuming you are returning two columns from  wk_GetPolicyData  Id and Carrier_Risk_Number
you will do something like


WHILE @@FETCH_STATUS = 0
BEGIN

insert into MyTemptable(ID int, Carrier_Risk_Number int)
EXEC wk_GetPolicyData @soc_sec_no, @policy_no, @effective_date

SET PreUpdateStatus=MyTemptable.Carrier_Risk_Number
From tblTEMP_tblIDIIncreaseUpload
Inner Join
          MyTemptable
ON     MyTemptable.ID=tblTEMP_tblIDIIncreaseUpload.ID

FETCH NEXT FROM curIterate INTO @soc_sec_no, @policy_no, @effective_date
END




-- and then

UPDATE tblTEMP_tblIDIIncreaseUpload


0
 
ironpen45Author Commented:
yes, Carrier_Risk_Number field comes from wk_GetPolicyData.

so there's no way of piping of the wk_GetPolicyData directly as an update to another table?

sorry for being stubborn. i vaguely remember an example some time ago. thanx!
0
 
rw3adminCommented:
with the exec wk_GetPolicyData you definetly need to capture the result set in a table (maybe temp or permenant) before you can use that data set for DML. now if Carrier_Risk_Number  is the ONLY field returned from the proc and you are Using an OUTPUT directive in the definition of wk_GetPolicyData proc then use you can capture this one value in a variable and use that to update tblTEMP_tblIDIIncreaseUpload .

Since you are leaning away from what I and other experts are saying, may I ask the reason why you think this is not a better idea, maybe we can come up with some other solution, if we know exactly what you want to do and how are application will behave if you go one way over other.

rw3admin
0
 
ironpen45Author Commented:
the reason is that i have to pipe/update between 27 and 32 fields, and wk returns over 50 fields (pretty complex calculations and not work replicating) and the process may change over time. just wanted to tidy up

also i forgot, rw3admin... just to be sure,
i cannot select which columns to use from wk_GetPolicyData as via the VALUES option? thanx again.
0
 
rw3adminCommented:
>>the process may change over time. just wanted to tidy up <<
just this line throws all design out the door, whatever you do right now is going to be a temp solution so my suggestion go for this design and when you are re-designing put your update statement in wk_GetPolicyData proc.

I dont understand this line
>>i cannot select which columns to use from wk_GetPolicyData as via the VALUES option? thanx again.<<

casue if you know data set returning from wk_GetPolicyData you will design your destination table accordingly to capture those fields.

Imran, AngelIII do you have anything to add to it?

rw3admin
0
 
ironpen45Author Commented:
thanx, but i can't modify anything in wk_GetPolicyData. i'm forbidden to tweak it.

what i meant by...
>>i cannot select which columns to use from wk_GetPolicyData as via the VALUES option? thanx again.<<
is that i want to insert data that is not in the same order as the columns and if, for example, i want to insert 2 columns into my temp table where the wk_GetPolicyData returns over 50.

thanx again!
0
 
rw3adminCommented:
yea I know its pretty tough working with limitation of not bieng able to edit code to fit your need, all I can say is create a destination table with 50 columns and use the two that you want, or if you have a frontend app like PHP pages you can capture data returned from the wk_GetPolicyData into an array and the update your tblTEMP_tblIDIIncreaseUpload  by stepping through that array, but still in array you will have 50 keys per record.

btw my example code was missing update table command not that it matters with you but the correct example should be

WHILE @@FETCH_STATUS = 0
BEGIN

insert into MyTemptable(ID int, Carrier_Risk_Number int)
EXEC wk_GetPolicyData @soc_sec_no, @policy_no, @effective_date

UPDATE tblTEMP_tblIDIIncreaseUpload
SET PreUpdateStatus=MyTemptable.Carrier_Risk_Number
From tblTEMP_tblIDIIncreaseUpload
Inner Join
          MyTemptable
ON     MyTemptable.ID=tblTEMP_tblIDIIncreaseUpload.ID

FETCH NEXT FROM curIterate INTO @soc_sec_no, @policy_no, @effective_date
END
0
 
ironpen45Author Commented:
thanx for your recommendation, rw3admin, and everyone else too. points to u guys...
0
 
ironpen45Author Commented:
why can't i split points here???
0
 
rw3adminCommented:
I dont know ... I have only one time asked a question on EE where I had to award points, and even that I messed up, angelIII and Imran are pretty good with it so maybe they can tell you how to split points.

rw3admin
0
 
ironpen45Author Commented:
so you think that the 'split points' option has been discontinued? i don't get it.
0
 
rw3adminCommented:
oh no not that....
check out following
http://www.experts-exchange.com/help.jsp#hi19
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now