Wednesday, November 4, 2009

Use Temp Table in SQL SERVER 2005 Reporting Services

I was working on 2005 SSRS to generate a reprot which required a use of Temporary Tables in the dataset. But unfortunately SSRS 2005 doesn't support it. 2008 supports both Table Datatype and Temp Tables.

To resolve the issue we can use Table DataType. Below is the example:

DECLARE @TempTable TABLE (
ID INT,
DOJ DATETIME,
NameVarchar(100)
)

INSERT INTO @TempTable VALUES(1, GETDATE(), 'Ranjit');
INSERT INTO @TempTable VALUES(2, GETDATE(), 'Ranjitsingh');

SELECT * FROM @TempTable
This way you can resolve the Temp table issues by using Table Datatype.

1 comment:

Andriy a33ik Butenko said...

You definitely can use Temp Tables in 2005.

Create TABLE #tmp(
ID INT,
DOJ DATETIME,
NameVarchar(100)
)

INSERT INTO #tmp VALUES(1, GETDATE(), 'Ranjit');
INSERT INTO #tmp VALUES(2, GETDATE(), 'Ranjitsingh');

SELECT * FROM #tmp
Dropt Table #tmp