Create Temp Table and Insert Data in to Temp Table using Dynamic Query

Create Temp Table and Insert Data in to Temp Table using Dynamic Query sp_executesql

 

 -- create temp table

 CREATE TABLE #tmp

 (

      Color VARCHAR(MAX),

      TmpDays VARCHAR(MAX),

      NoofOpp VARCHAR(MAX)

     

      )

 DECLARE @dynamicSQL nvarchar(2000),

 @Numberofdays BIGINT = NULL

 SET @Numberofdays = 8

 --Create Dynamic Query

 SET @dynamicSQL = 'SELECT CASE

                                WHEN Color = ''LIGHT_GREEN'' THEN ''#90EE90''

                                WHEN Color = ''GREEN'' THEN ''#006400''

                                WHEN Color = ''LIGHT_BLUE'' THEN ''#ADD8E6''

                                WHEN Color = ''BLUE'' THEN ''#0000CD''

                                WHEN Color = ''LIGHT_ORANGE'' THEN ''#FFA500''

                                WHEN Color = ''ORANGE'' THEN ''#FF4500''

                                WHEN Color = ''RED'' THEN ''#FF0000''

                           END AS Color,

                           CASE       

                                WHEN Color = ''LIGHT_GREEN'' THEN ''0-7 Days''

                                WHEN Color = ''GREEN'' THEN ''8-15 Days''

                                WHEN Color = ''LIGHT_BLUE'' THEN ''16-25 Days''

                                WHEN Color = ''BLUE'' THEN ''26-35 Days''

                                WHEN Color = ''LIGHT_ORANGE'' THEN ''36-45 Days''

                                WHEN Color = ''ORANGE'' THEN ''46-60 Days''

                                WHEN Color = ''RED'' THEN ''60 > Days''

                           END AS TmpDays,

                           COUNT       (Opportunity_Code) AS NoofOpp

                    FROM   dbo.View_OpportunityHeaderDetails

                    WHERE  [Status] != ''deleted''

                           AND VersionNo = 0 '

                          

                           IF @Numberofdays IS NOT NULL

                                    SET @dynamicSQL =  @dynamicSQL + '  AND DATEDIFF(dd, LastmodifyDate,GETDATE()) > ' + CONVERT(NVARCHAR(MAX), @Numberofdays ) + '  GROUP BY  Color '

                           ELSE

                           SET @dynamicSQL = @dynamicSQL + ' GROUP BY  Color

                          

                           PRINT @dynamicSQL

                      -- Execute Dynamic Query.

                     INSERT INTO #tmp  EXEC sp_executesql @dynamicSQL

                    

                     SELECT * FROM #tmp      

                          

                         

Ebook Download
View all
Learn
View all