SQL Server 2008 - Stored Procedure that triggers another stored procedure

Question

Post reply

SQL Server 2008 - Stored Procedure that triggers another stored procedure

charleserasmus3

Newbie

Points: 2
More actions
February 23, 2015 at 10:18 am

#398061

Hi Guys,
My name is Charles and I have a little issue with a small SQL server 2008 project.
I have 1 stored procedure that updates table1 and takes 2 parameters, @ID and @Salary. I have a trigger on table1 that calls SP_2 after update. Before the update in SP_1 a value is stored as @TempVariable then changed by the update. I need to use this value in SP_2 that is called by the trigger on table 1. @TempVariable would be one of the parameters in SP_2. But I cant use EXEC SP_2 in the trigger because @TempVariable is not declared within the trigger. Could anyone use this information to provide me with an easy example please. It would be greatly appreciated.
CREATE PROCEDURE OldSalary @EmpID INT = NULL, @LatestSalary INT = NULL, @TempVariable INT = NULL OUTPUT
AS
UPDATE SalaryHistory-- 1ST, UPDATE "OldSalary" with "CurrentSalary" VALUE FOR TRACKING
SET OldSalary = @TempVariable
WHERE SalaryHistory.EmpID = @EmpID
UPDATE SalaryHistory
SET NewSalary = @LatestSalary, DateAdjusted = CURRENT_TIMESTAMP
WHERE EmpID = @EmpID;
GO
CREATE PROCEDURE Adjust_Salary @EmpID INT = NULL, @LatestSalary INT = NULL-- CREATE MAIN UPDATE PROCEDURE
AS
DECLARE @TempVariable INT = (SELECT CurrentSalary FROM Employees WHERE EmpID = @EmpID)
/*EXEC OldSalary @EmpID = @EmpID, @LatestSalary = @LatestSalary, @TempVariable = @TempVariable;*/
UPDATE Employees-- 2ND, UPDATE CurrentSalary WITH @LatestSalary
SET CurrentSalary = @LatestSalary
WHERE EmpID = @EmpID;
RETURN @TempVariable
RETURN @LatestSalary
RETURN @EmpID
GO
I need a trigger that would call "OldSalary" after updating Employees in "Adjust_Salary". Please help.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

you can call a procedure within a procedure.

you have a logical error in your RETURN statmenets; there can be onyl one return:

CREATE PROCEDURE Adjust_Salary @EmpID INT = NULL, @LatestSalary INT = NULL-- CREATE MAIN UPDATE PROCEDURE

AS

DECLARE @TempVariable INT = (SELECT CurrentSalary FROM Employees WHERE EmpID = @EmpID)

/*EXEC OldSalary @EmpID = @EmpID, @LatestSalary = @LatestSalary, @TempVariable = @TempVariable;*/

UPDATE Employees-- 2ND, UPDATE CurrentSalary WITH @LatestSalary

SET CurrentSalary = @LatestSalary

WHERE EmpID = @EmpID;

--call the child procedure as part of this business process

EXECUTE OldSalary @EmpID,@LatestSalary,@TempVariable

RETURN @TempVariable

RETURN @LatestSalary --<<--the code will never each this line

RETURN @EmpID --<<--the code will never each this line

GO

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!

charleserasmus3 Newbie Points: 2 More actions · Answer 2

Sorry about the logical error. That was just me trying to figure it out and then copying it. I have gotten it so far that the 2 procedures do the task I require but I need the update from the second procedure I create to trigger the first procedure that updates another table with a value stored in the second procedure. Could you help me with returning that value/variable and calling it in the first procedure? So it can be used to update another table. I'm also struggling to to execute the first procedure with the same parameters as the second procedure ( I can't use them to EXECUTE the procedure in the trigger because the variables don't exist )