Microsoft Business Intelligence: Executing SQL Stored Procedure from another Stored Procedure

Saturday 22 May 2021

Executing SQL Stored Procedure from another Stored Procedure

We have an application in that we have around 10 modules. For each module, we have one sp to populate the data. Our requirement is that after the SP call we need to log in the information in the log table like module name, user name and access time.

To achieve this we have two approaches.

o We need to write the query to insert the record into the log table in each Sp

o We need to write on sp to log the information into the table and we will call this sp in each module sp.

The drawback for approach one is if we made any change in the logic we need to make the changes in all SP. But in the second approach, we can make the change in one place only.

Let’s see in this demo how we call one sp from another SP.

We have a log table

Now writing an SP to insert the records in this table.

CREATE PROCEDURE [dbo].[P_insert_app_audit] @App_nm  VARCHAR(50),
                                            @user_nm VARCHAR(50)
AS
  BEGIN
      INSERT [dbo].[errorlog]
             ([username],
              [errornumber],
              [errorprocedure],
              [errormessage])
      VALUES ( @user_nm,
               1,
               @App_nm,
               @App_nm + ' is called by the user ' + @user_nm
               + ' at '
               + Cast(CURRENT_TIMESTAMP AS VARCHAR(50)) );
  END;

We are calling this SP into the other SP.

CREATE PROCEDURE [dbo].[P_get_productid] @ProductID VARCHAR(20)
AS
  BEGIN
      DECLARE @return_val BIGINT=0;

      SELECT productid,
             NAME,
             productnumber,
             listprice,
             size
      FROM   [Production].[product]
      WHERE  productid = @ProductID

      ----calling the another SP P_Insert_App_Audit
      EXEC @return_val=P_insert_app_audit
        @App_nm = 'Product',
        @user_nm ='Bagi';

      IF @return_val <> 0
        BEGIN
            RAISERROR(
            'there is some error while calling the SP P_Insert_App_Audit',16
            ,1
            );
        END
  END;