Hi,

Thanks for the replies. I am posting the code where I got error

CREATE TABLE #TempTable

(

Demand NUMERIC(8, 0),

Capacity NUMERIC(8, 0),

MonthAndYear VARCHAR(20)

)

SET @SqlQry = N'INSERT INTO #TempTable(Demand, Capacity, MonthAndYear)

SELECT COUNT(RECVD_DTTM) AS Demand, NULL,

CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) AS MonthAndYear FROM temp_18ww_DemandCapacity ' + @WhereSearchString + '

GROUP BY CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) '

EXEC sp_executesql @SqlQry

-- Get the total pending referrals and update the local table

SET @SqlQry = N'UPDATE tmp SET Capacity = t.TotalCapacity FROM #TempTable tmp INNER JOIN

(

SELECT COUNT(RECVD_DTTM) AS TotalCapacity,

CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) AS MonthAndYear

FROM temp_18ww_DemandCapacity_WaitList

WHERE Wait_List_Type = ''PENDING'' ' + @SearchString + '

GROUP BY CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4))

) t ON t.MonthAndYear = tmp.MonthAndYear'

EXEC sp_executesql @SqlQry

SELECT * FROM #TempTable

I am getting error where ever I used #TempTable except at CREATE TABLE statement.

whats the problem. Is there any other way to achieve this?

thanks