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