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