Wednesday 21 November 2012

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

4 comments:

  1. Yes we can use it using temporary table as well as normal table.... Remember we often face the question "How can we copy data or without data from a table to another table" ...
    It is a very common question in any Sql Server interviwe question. Now the answer of this problem also lie in this solution.

    here is the Solution....

    DECLARE @sqlString NVARCHAR(200)
    DECLARE @Attribute NVARCHAR(200)
    DECLARE @TableName NVARCHAR(20)

    SET @Attribute='EmployeeId,EmployeeName,EmployeeCode'
    SET @TableName='Employee'
    IF OBJECT_ID('##Emp_Temp2') IS NOT NULL
    BEGIN
    DROP Table ##Emp_Temp2

    SET @sqlString='SELECT '+@Attribute+ '
    Into ##Emp_Temp2 FROM '+ @TableName


    EXEC (@sqlString)

    SELECT * FROM ##Emp_Temp2
    END
    GO





    go

    ReplyDelete
    Replies
    1. Hello “Saikat Mitra”

      Thanks for your interest and comments.
      But the global Temporary table that I don’t needed here. Actually I am looking for Local Temporary Table (with single #). As the global temporary table have its own complexity in multi user environment.

      Delete
  2. Yes I have examined it using the same code .
    In case of using local temporary table .SSMS give error msg invalid object..
    that means when we execute " Select * from #Emp_Temp2" then #Emp_Temp2 already out of scope.

    So, if we execute total statements in a single execute statement then it works...

    Here is the code..

    BEGIN
    DECLARE @sqlString NVARCHAR(200)
    DECLARE @Attribute NVARCHAR(200)
    DECLARE @TableName NVARCHAR(20)

    SET @Attribute='EmployeeId,EmployeeName,EmployeeCode'
    SET @TableName='Employee'
    --DECLARE @Emp_Temp2 TABLE

    SET @sqlString='SELECT '+@Attribute+ ' Into #Emp_Temp2 FROM '+ @TableName+'; SELECT * FROM #Emp_Temp2'


    EXEC (@sqlString)

    --SELECT * FROM #Emp_Temp2
    END

    ---So in this case we can use local temporary table..

    ReplyDelete
    Replies
    1. Saikat,
      Thanks for you reply.
      The approach you made is correct but you can't use the #Emp_temp2 within the Stored Procedure.
      I want to be use this temporary table within the stored procedure.
      Is there any implementation process of that?

      Delete