Categories
SQL / Stored Procedures SQL Server 2000

Execute a stored procedure from another stored procedure

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.

  • sp_update
  • sp_delete
  • sp_common
  • 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

    Exit mobile version