Ability to update multiple tables simultaneously via stored proc

Anyone have any ideas on how to use a stored procedure to update multiple tables simultaneously? I am updating a parent record and zero or more child records. I would like to make one stored procedure call if possible to do so. Any ideas on doing this would be appreciated. Thanks!

EverettYou can update more than one table within a stored procedure, just make sure to use BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN

Example:

CREATE PROCEDURE sp_ModifyMasterDetail
(
.
.
.
@.msg varchar(255) output
)
AS
SET NOCOUNT ON

DECLARE @.error int
, @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SELECT @.tfTran = 1
BEGIN TRAN
END
ELSE
SELECT @.tfTran = 0

UPDATE tblMaster
.
WHERE ID = @.ID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

UPDATE tblDetail
.
WHERE ID = @.ID
AND SubID = @.SubID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

--
-- Check to see if an error occured during processing. If so then
-- ROLLBACK else COMMIT transactions
--
Error_Exit:

IF (@.error <> 0) BEGIN
IF (@.tfTran = 1)
ROLLBACK TRAN

SELECT @.msg = 'ERROR: Transaction failed with error ' + CONVERT(varchar(20),@.error),
END
ELSE BEGIN
IF (@.tfTran = 1)
COMMIT TRAN

SELECT @.msg = 'Transaction successful'
END

RETURN @.error
GO|||begin tran
update parent set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update child set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

Or you could put a trigger on the parent (or child) table or on a view of the combination - depends on the updates you want to do.|||Thanks guys! Either one of these will do the trick, except that I'm not sure how to get the data into the stored procedure! I guess I could munge it into varchar(8000), but I'm not sure that it would always be long enough. Any way of passing either an array or a recordset/cursor into a stored procedure?

Everett|||You can create a temp table on the spid, populate it then access it in the SP.

Call the SP repeated times with the values and the SP can populate a table keyed on spid.

Call the sp with comma delimitted strings with the values.

Have lots of parameters - up to the max you think you will need.