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

Question

Using SQL Server 2008 Reporting Services:

I'm trying to write a report that displays some correlated data, so I thought to use the @table variable like this

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 continues to query the value of the @Results variable. Is it possible?

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

Answer 1

No.

ReportBuilder will be

• Suppose you
• @Results

Answer 2

. , ( ) #temp, Number ( Number + Name, ).

SELECT , ( ):

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

Answer 3

SSRS, , , "" ( ):

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) '#', type, count (type) FROM title GROUP BY type ORDER BY type SELECT @cntMax = max (#) @varTable

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

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

SELECT @RowsToProcess = COUNT (*) @varTable

WHILE @CurrentRow < @RowsToProcess
  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

Answer 4

Why can't you just JOIN two sets of results?

Answer 5

How about using a table-valued function rather than a stored procedure?

Answer 6

I also saw this problem. SQLRS seems to be a little case sensitive. If you guarantee that your table variable is declared and referenced throughout with the same literal case, you will skip the prompt for the parameter.