Updating a table using a Stored Procedure as source
-
Hello,
I have an update statement such that:
([dbo].[myStoredProcedure] takes one parameter, a date)
update dbo.mytable
set
mytable.one = source.uno
,mytable.two = source.due
,mytable.three = source.tre
from
(exec [dbo].[myStoredProcedure] GetDate()) source
where
mytable.dte = source.dte
source.type = 'BUY'
(I've added the alias' for readability - i think it does work in practice but for this exercise am more interested in the FROM clause)
It's not working...
1) Can is work?
2) Is it the right approach?
3) What would you do?
Thanks!
-
Hi,
1) It will not work.
2)It is not a right approach
It would be good to make a user defined function in place of stored proceduer and used it.
cheers
-
i believe you need to create a temp table to capture teh results of the stored proc as one step, and then update from the temp table.
alternatively, you might be able to reqwrite the procedure to be a function that returns a table...then you can do an update like you've done above, replacing the exec [dbo].[myStoredProcedure] GetDate()) source
with select * from dbo.myFunction(getdate() ) as source
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you both for very considerate responses.
To stay in line with the other objects I've created, I believe that I prefer the temp table approach... How would that work considering the Stored Procedure must receive an argument (I've never used a temp table) ?
(pseudo code will be appreciated
) -
Ahhh, I think I have it:
CREATE TABLE #mySource (
dte varchar (8)
,uno int
,due int
,tre int
,type varchar(4) )
INSERT #mySource (
dte
,uno
,due
,tre
,type)
EXECUTE [dbo].[myStoredProcedure] GetDate()
update dbo.mytable
set
mytable.one = source.uno
,mytable.two = source.due
,mytable.three = source.tre
from
(exec [dbo].[myStoredProcedure] GetDate()) source
where
mytable.dte = source.dte
source.type = 'BUY'
-
This might work better :
update MT set
MT.one = MS.uno
,MT.two = MS.due
,MT.three = MS.tre
from dbo.mytable MT INNER JOIN #MySource MS ON MT.dte = MS.dte
where source.type = 'BUY'
-
Please, why so?
-
from
(exec [dbo].[myStoredProcedure] GetDate()) source
where
This code of code is forbidden. You have to use the temp tables you just created to do the update.
-
Ahh I see... I didn't provide you with my final corrected Pseudo code:
update dbo.mytable
set
mytable.one = source.uno
,mytable.two = source.due
,mytable.three = source.tre
from
#mySource source
where
mytable.dte = source.dte
and
source.type = 'SELL'
I assumed you saw the above and were advocating an explicit INNER JOIN rather than the implicit join that I actually used.
If you see a problem with this however, please let me know.
It does appear to work though.
-
Ya that should do it. The explicit join is always better, especially in avoiding unwanted cross joins. I was just pointing out again that the exec (spname) can't work the way is was presented.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply