Can I use @table variable in SQL Server Report Builder?

Using SQL Server 2008 Reporting services:

I'm tryin开发者_StackOverflow社区g to write a report that displays some correlated data so I thought to use a @table variable like so

DECLARE @Results TABLE (Number int
                       ,Name nvarchar(250)
                       ,Total1 money
                       ,Total2 money
                       )

insert into @Results(Number, Name, Total1)
select number, name, sum(total)
from table1
group by number, name

update @Results
set total2 = total
from
(select number, sum(total) from table2) s
where s.number = number

select from @results

However, Report Builder keeps asking to enter a value for the variable @Results. It this at all possible?

EDIT: As suggested by KM I've used a stored procedure to solve my immediate problem, but the original question still stands: can I use @table variables in Report Builder?

No.

ReportBuilder will

1. 2nd guess you
2. treats @Results as a parameter

Put all of that in a stored procedure and have report builder call that procedure. If you have many rows to process you might be better off (performance wise) with a #temp table where you create a clustered primary key on Number (or would it be Number+Name, not sure of your example code).

EDIT
you could try to do everything in one SELECT and send that to report builder, this should be the fastest (no temp tables):

select
    dt.number, dt.name, dt.total1, s.total2
    from (select
              number, name, sum(total) AS total1
              from table1
              group by number, name
         ) dt
        LEFT OUTER JOIN (select
                             number, sum(total) AS total2
                             from table2
                             GROUP BY number --<<OP code didn't have this, but is it needed??
                        ) s ON dt.number=s.number

I've seen this problem as well. It seems SQLRS is a bit case-sensitive. If you ensure that your table variable is declared and referenced everywhere with the same letter case, you will clear up the prompt for parameter.

You can use Table Variables in SSRS dataset query like in my code where I am adding needed "empty" records for keep group footer in fixed postion (sample use pubs database):

DECLARE @NumberOfLines INT
DECLARE @RowsToProcess INT
DECLARE @CurrentRow INT
DECLARE @CurRow INT
DECLARE @cntMax INT
DECLARE @NumberOfRecords INT
DECLARE @SelectedType char(12)
DECLARE @varTable TABLE (# int, type char(12), ord int) 
DECLARE @table1 TABLE (type char(12), title varchar(80), ord int )

DECLARE @table2 TABLE (type char(12), title varchar(80), ord int )  


INSERT INTO @varTable
SELECT count(type) as '#', type, count(type) FROM titles GROUP BY type ORDER BY type
SELECT @cntMax = max(#) from @varTable

INSERT into @table1 (type, title, ord) SELECT type, N'', 1 FROM titles
INSERT into @table2 (type, title, ord) SELECT type, title, 1 FROM titles 

SET @CurrentRow = 0
SET @SelectedType = N''
SET @NumberOfLines = @RowsPerPage

SELECT @RowsToProcess = COUNT(*) from @varTable 

WHILE @CurrentRow < @RowsToProcess
BEGIN

    SET @CurrentRow = @CurrentRow + 1

SELECT TOP 1 @NumberOfRecords = ord, @SelectedType = type 
FROM @varTable WHERE type > @SelectedType

SET @CurRow = 0

WHILE @CurRow < (@NumberOfLines - @NumberOfRecords % @NumberOfLines) % @NumberOfLines

BEGIN
    SET @CurRow = @CurRow + 1

    INSERT into @table2 (type, title, ord) 
    SELECT type, '' , 2
    FROM @varTable WHERE type = @SelectedType
END
END 

SELECT type, title FROM @table2 ORDER BY type ASC, ord ASC, title ASC

Why can't you just UNION the two resultsets?

How about using a table valued function rather than a stored proc?

It's possible, only declare your table with '@@'. Example:

DECLARE @@results TABLE (Number int
                       ,Name nvarchar(250)
                       ,Total1 money
                       ,Total2 money
                       )

insert into @@results (Number, Name, Total1)
select number, name, sum(total)
from table1
group by number, name

update @@results
set total2 = total
from
(select number, sum(total) from table2) s
where s.number = number

select * from @@results