You can execute a stored procedure from another stored procedure. This means that you can create a common stored procedure so that any stored procedure which needs this common stored procedure can execute it.
Example, you have these three stored procedures. One is a common one. The other two are normal one.
Inside sp_update, you have
UPDATE mytable SET status ‘N’
Inside sp_delete, you have
@ID int
AS
DELETE FROM mytable WHERE ID = @ID
Inside sp_common, you have
@ID int,
@desc varchar(3000),
@submit_by varchar(50),
@submit_by varchar(50)AS
INSERT INTO mytable (id, desc, submitby, submitdt, status)
VALUES (@ID, @desc, @submit_by, @submit_dt, ‘N’)return 1
If sp_update and sp_delete need sp_common, just add one more line (if there is no return value).
Inside sp_update, add the line in bold
UPDATE mytable SET status ‘N’
EXEC sp_common
Note: You can use either EXEC or EXECUTE. They are the same. But EXEC() is a different thing.
If there is return value, then inside sp_update, add a declaration line and edit the existing execution line (in bold):
DECLARE @status int
UPDATE mytable SET status ‘N’
EXEC @status = sp_common