Dynamic SQL result stored in a TABLE

Dynamic SQL result stored in a TABLE

Introduction

As we all know that the dynamic SQL is not good where performance is concern. But sometimes we don’t have other choices. As I personally think that, no developer chooses the dynamic SQL with interest, but they choose it as they have no alternates to minimize the code. Well the debugging of dynamic SQL is not so easy.  

Some times when the developer works with stored procedure they want to make certain dynamic SQL and want to copy the output or the result set of the dynamic SQL into a table and next want to work with table for farther processing.

Please note that the Temporary Table is not permitted here.

The Problem
As the columns name and data type of the dynamic SQL is dynamic, it is not possible to make a table definition within the stored procedure.
To understand it properly here I am providing an example.
— Base Table Object Creation
IF OBJECT_ID(‘my_EMP’) IS NOT NULL
   BEGIN
     DROP TABLE my_EMP;
   END
GO  
CREATE TABLE my_EMP
       (EMPID    INT         NOT NULL IDENTITY PRIMARY KEY,
        EMPNAME  VARCHAR(50) NOT NULL,
        EMPGRADE VARCHAR(1)  NOT NULL,
        EMPDEPT  VARCHAR(50) NOT NULL,
        EMPCITY  VARCHAR(50) NOT NULL,
        EEMSTATE VARCHAR(50) NOT NULL);
GO
— Inserting some Records
INSERT INTO my_EMP
       (EMPNAME, EMPGRADE, EMPGRADE, EMPDEPT, EMPCITY, EEMSTATE)      
VALUES (‘JOYDEEP DAS’, ‘B’, ‘DEV’, ‘AGARTALA’, ‘TRIPURA’),
       (‘TUHIN SHINAH’, ‘B’, ‘DEV’, ‘KOLKATA’, ‘WEST BENGAL’),
       (‘SANGRAM JIT’, ‘B’, ‘DEV’, ‘KOLKATA’, ‘WEST BENGAL’),
       (‘SUKAMAL JANA’, ‘B’, ‘DEV’, ‘CHUCHURA’, ‘WEST BENGAL’),
       (‘SUDIP DAS’, ‘A’, ‘MGR’, ‘KOLKATA’, ‘WEST BENGAL’),
       (‘SAIKAT SREE’, ‘A’, ‘MGR’, ‘KOLKATA’, ‘WEST BENGAL’),
       (‘MANI SANKAR’, ‘C’, ‘DM’, ‘AGARTALA’, ‘TRIPURA’),
       (‘A DEKA’, ‘C’, ‘DM’, ‘GWAHATI’, ‘ASSAM’);
GO

— Creting Stored Procedure with Dynamic SQL
— Note that the parameters, one is the fields name and second is the Table objects
— So the Dynamic Sql fields and table are dynamic and depends on user input.  

IF OBJECT_ID(‘usp_DISPLAYEMP’) IS NOT NULL
   BEGIN
     DROP PROCEDURE usp_DISPLAYEMP;
   END
GO         

CREATE PROCEDURE usp_DISPLAYEMP
       (
          @p_Attributes VARCHAR(200) = NULL,
          @p_TblName    VARCHAR(50)  = NULL
       )
AS
  DECLARE @sqlString VARCHAR(200);
  BEGIN
   IF ISNULL(@p_Attributes, )<> AND ISNULL(@p_TblName, )<>
     BEGIN
         SET @sqlString=‘SELECT ‘+ @p_Attributes + ‘ FROM ‘+ @p_TblName;
         EXEC (@sqlString);
     END
   ELSE
     BEGIN
        PRINT ‘Input can not be Null’;
     END   
  END
— Executing the SP [ with Fields name EMPNAME, EMPGRADE, EMPGRADE ]
EXEC usp_DISPLAYEMP
     @p_Attributes = ‘EMPNAME, EMPGRADE, EMPGRADE’,
     @p_TblName    = ‘my_EMP’

The output is
EMPNAME                                            EMPGRADE EMPGRADE
————————————————– ——– ——–
JOYDEEP DAS                                        B        B
TUHIN SHINAH                                       B        B
SANGRAM JIT                                        B        B
SUKAMAL JANA                                       B        B
SUDIP DAS                                          A        A
SAIKAT SREE                                        A        A
MANI SANKAR                                        C        C
A DEKA                                             C        C
(8 row(s) affected)
— Executing the SP [ with Fields All Fields Name ]    
EXEC usp_DISPLAYEMP
     @p_Attributes = ‘*’,
     @p_TblName    = ‘my_EMP’
The Output is
EMPID     EMPNAME        EMPGRADE  EMPDEPT    EMPCITY       EEMSTATE
—————————– —————————————
1         JOYDEEP DAS    B         DEV         AGARTALA     TRIPURA
2         TUHIN SHINAH   B         DEV         KOLKATA      WEST BENGAL
3         SANGRAM JIT    B         DEV         KOLKATA      WEST BENGAL
4         SUKAMAL JANA   B         DEV         CHUCHURA     WEST BENGAL
5         SUDIP DAS      A         MGR         KOLKATA      WEST BENGAL
6         SAIKAT SREE    A         MGR         KOLKATA      WEST BENGAL
7         MANI SANKAR    C         DM          AGARTALA     TRIPURA
8         A DEKA         C         DM          GWAHATI      ASSAM
(8 row(s) affected)
So in this example the output of the stored procedure depends on user input. We can’t understand how many columns it displays.
We want to use a table within this stored procedure.
Easy Solutions
Example-1
— Using Temp Table    
IF OBJECT_ID(‘usp_DISPLAYEMP’) IS NOT NULL
   BEGIN
     DROP PROCEDURE usp_DISPLAYEMP;
   END
GO         
CREATE PROCEDURE usp_DISPLAYEMP
       (
          @p_Attributes VARCHAR(200) = NULL,
          @p_TblName    VARCHAR(50)  = NULL
       )
AS
  DECLARE @sqlString VARCHAR(200);
  BEGIN
     IF ISNULL(@p_Attributes, )<> AND ISNULL(@p_TblName, )<>
        BEGIN
         SET @sqlString=‘SELECT ‘+ @p_Attributes + 
                        ‘ INTO TMP_TBL FROM ‘+@p_TblName;
         EXEC (@sqlString);
        END
     ELSE
        BEGIN
            PRINT ‘Input can not be Null’;
        END   
  END 
Example-2
— Using Temp Table    
IF OBJECT_ID(‘usp_DISPLAYEMP’) IS NOT NULL
   BEGIN
     DROP PROCEDURE usp_DISPLAYEMP;
   END
GO         
CREATE PROCEDURE usp_DISPLAYEMP
       (
          @p_Attributes VARCHAR(200) = NULL,
          @p_TblName    VARCHAR(50)  = NULL
       )
AS
  DECLARE @sqlString VARCHAR(200);
  BEGIN
     IF ISNULL(@p_Attributes, )<> AND ISNULL(@p_TblName, )<>
        BEGIN
             SET @sqlString=‘SELECT ‘+ @p_Attributes +
                            ‘ INTO ‘ + TMP_TBL +
                            ‘ FROM ‘+ @p_TblName;
             EXEC (@sqlString);
        END
     ELSE
        BEGIN
            PRINT ‘Input can not be Null’;
        END   
  END            
Share your Knowledge
If we can put the output in a Temporary Table (the Local Temporary Table) it well is the BEST.
But in this situation it is NOT possible. If have you an idea, please share your knowledge with us.
Related Tropics
Dynamic SQL With sp_executesql ()
Hope you like it.
Posted by: MR. JOYDEEP DAS