Microsoft Business Intelligence: Temp table mystery: Shall we call the Parent Temp table into the Child SP

Tuesday, 25 May 2021

Temp table mystery: Shall we call the Parent Temp table into the Child SP

Yes, we can call the Parent temp table in the child SP. We can’t call the Child SP Temp table in the parent SP.

Let’s see this demo.

Here we have created two 2SP

Child SP

CREATE PROCEDURE [dbo].[P_child_sp_demo]
AS
  BEGIN
      CREATE TABLE #child_temp
        (
           id   INT IDENTITY(1, 1) PRIMARY KEY,
           NAME VARCHAR(50),
           eadd VARCHAR(100)
        )

      INSERT INTO #child_temp
                  (NAME,
                   eadd)
      SELECT 'Child SP Bagesh',
             'Noida'
      UNION ALL
      SELECT 'Child SP Rajesh',
             'Pune'

      SELECT *
      FROM   #child_temp
  END;

Running this SP.

Now creating the Parent SP

CREATE PROCEDURE [dbo].[P_parent_sp_demo]
AS
  BEGIN
      DECLARE @return_val INT =0;

      CREATE TABLE #parent_temp
        (
           id   INT IDENTITY(1, 1) PRIMARY KEY,
           NAME VARCHAR(50),
           eadd VARCHAR(100)
        )

      INSERT INTO #parent_temp
                  (NAME,
                   eadd)
      SELECT 'Parent SP Bagesh',
             'Noida'
      UNION ALL
      SELECT 'Parent SP Rajesh',
             'Pune'

      SELECT *
      FROM   #parent_temp

      ------calling the another SP P_Child_SP_Demo
      EXEC @return_val=P_child_sp_demo

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

Now we are calling the Temp table in the Child SP which we have created in the Parent table.

Altering the child SP

ALTER PROCEDURE [dbo].[P_child_sp_demo]
AS
  BEGIN
      CREATE TABLE #child_temp
        (
           id   INT IDENTITY(1, 1) PRIMARY KEY,
           NAME VARCHAR(50),
           eadd VARCHAR(100)
        )

      INSERT INTO #child_temp
                  (NAME,
                   eadd)
      SELECT 'Child SP Bagesh',             'Noida'
      UNION ALL
      SELECT 'Child SP Rajesh',             'Pune'

      SELECT *      FROM   #child_temp

      SELECT *      FROM   #parent_temp
  END;

Now we are calling the Parent SP.

SP executed successfully.

If we execute only child P alone we will get the error.

When we call the child SP temp table we are getting the error. Altering the Parent SP.

ALTER PROCEDURE [dbo].[P_parent_sp_demo]
AS
  BEGIN
      DECLARE @return_val INT =0;

      CREATE TABLE #parent_temp
        (
           id   INT IDENTITY(1, 1) PRIMARY KEY,
           NAME VARCHAR(50),
           eadd VARCHAR(100)
        )

      INSERT INTO #parent_temp
                  (NAME,
                   eadd)
      SELECT 'Parent SP Bagesh',
             'Noida'
      UNION ALL
      SELECT 'Parent SP Rajesh',
             'Pune'

      SELECT *
      FROM   #parent_temp

      ------calling the another SP P_Child_SP_Demo
      EXEC @return_val=P_child_sp_demo

      SELECT *      FROM   #child_temp

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