What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@.EntryLogID int,
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2)
)
AS
DECLARE@.OldCategoryID int
EXEC @.OldCategoryID = BP_GetLedgerCategory @.EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @.ProjectID,
CategoryID = @.NewCategoryID,
Expended = @.Expended
WHERE
EntryLogID = @.EntryLogID
EXEC BP_UpdateCategories @.ProjectID, @.NewCategoryID, @.Expended, @.OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@.EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @.EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2),
@.OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.OldCategoryIDyou need to use OUTPUT parameters to return a value..check BOL (Books On Line ) its a free download from microsoft...check for correct synax and documentation...
hth|||Thank you...though...I found more info later last night after I came across the phrase 'OUTPUT' parameters.
This thread in particular ...
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=399995
My sproc is finished and I've moved on...
