execute stored proc from within stored proc
-
Hi all,
I'm a beginner to sql stored proc and my question is:
1. I have two stored proc:
A) stored proc with a SELECT FROM statement that I then use
with a recordset object in an ASP page.
B) stored proc with a UPDATE TABLE statement that get parameters
and update tables.
2. In one particular case I want to update a row (forum thread for example)
and then update all rows that connected (child threads for example). So I
thought about making a new stored proc that execute the SELECT stored
and then loop over the recordset and for each row call the UPDATE stored
proc.
(1) is this way better or faster than to do it directly from a ASP page (what
I actualy doing now)??
(2) If so, how do I do it? what is the syntax?
TNX.
-
It depends on what is being returned from the select statement. If you are only expecting one row back you could use:
------------------------------------------------------------------
CREATE PROC NAME_YOU_LIKE
Parameters Parm_type
as
DECLARE @ret parm_type
SELECT @ret=Name_of_Colum FROM tableTOSelect WHERE CONDITION=TRUE
UPDATE tableName Set ColumnName=@ret WHERE CONDITION =TRUE
or
Exec OtherStoredProc @ret
------------------------------------------------------------------------
Using This Method also allows you to use @ret in the Stored Proc in other places in your stored proc
-
No, the select proc return multiple rows and I want to loop through them
and update each data at a time.
TNX
-
To do this, you need to:
1. Create a temp table with columns that match the output of the Stored Procedure.
CREATE TABLE #recordset(
ident INT IDENTITY(1,1) PRIMARY KEY,etc,etc)
2. Do an INSERT...EXEC. You have to use a column list on this. So:
INSERT #recordset(etc, etc) EXEC sp_whatever @whatever
3. Loop through the records by setting one variable to be the max(ident) and the other to be 1. Use a WHILE loop WHILE @counter <= @max-2 BEGIN whatever SELECT @counter = @counter + 1 END
Make sense?
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
You should be able to do one multi-row UPDATE, since you want to modify all the child records of a certain record...just construct the WHERE clause accordingly.
UPDATE <table>
SET whatever='whatever',...n
WHERE ParentID='ParentID'
I don't know exactly how your table is set up, but something like that should be possible, and much faster than looping.
-
I have to loop because I want to update the child's child's as well (and so on), so I have no common field (at least thats how my table is built...)
Do you think doing the loop from a stored proc like suggested above is
prefferable than doing it from the ASP page ?
-
If you gotta do a loop, you might as well do it in ASP. Doing it in a SPROC may or may not be faster, but probably not by much.
I'm still not convinced that there isn't a set-based solution without knowing your table structure though. Maybe there's some way to do JOINs so that you could do it all with one UPDATE.
-
Just had an Idea! I think it should be simple:
The SELECT stored proc that I have selects all the records I need to update. So if I alter this proc to just return a string of all the IDs I can use WHERE ID IN() statement to update them all at once.
It will have to wait for Sunday though... till I'll get back to work.
Thanks anyway.
-
Ok, but I can't imagine any reason why you could SELECT, but not UPDATE these records.
You should be able to take:
SELECT ...
FROM ...
WHERE ...
and turn it into
UPDATE ...
FROM ...
WHERE ...
with everything after "FROM" being the same in both statements and it should work. At least, I can't imagine any situation where it wouldn't work.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply