Solved

running EXEC results into a table

Posted on 2006-11-17
18
462 Views
Last Modified: 2008-02-01
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
Comment
Question by:ironpen45
18 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 17966155
>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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17967063
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
 

Author Comment

by:ironpen45
ID: 17967096
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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17967149
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
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 50 total points
ID: 17971618
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
 

Author Comment

by:ironpen45
ID: 17996383
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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17996502
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
 

Author Comment

by:ironpen45
ID: 17996544
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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17996646
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ironpen45
ID: 17996691
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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17996808
>>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
 

Author Comment

by:ironpen45
ID: 17996860
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
 
LVL 11

Accepted Solution

by:
rw3admin earned 250 total points
ID: 17996913
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
 

Author Comment

by:ironpen45
ID: 17996962
thanx for your recommendation, rw3admin, and everyone else too. points to u guys...
0
 

Author Comment

by:ironpen45
ID: 17996973
why can't i split points here???
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17997048
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
 

Author Comment

by:ironpen45
ID: 17997066
so you think that the 'split points' option has been discontinued? i don't get it.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17997080
oh no not that....
check out following
http://www.experts-exchange.com/help.jsp#hi19
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

9 Experts available now in Live!

Get 1:1 Help Now