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

Using SQL Server 2008 Reporting services:

I'm trying 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?

sql-serverreporting-servicesreport-designer


Solution 1:[1]

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

Solution 2:[2]

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.

Solution 3:[3]

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

Solution 4:[4]

Why can't you just UNION the two resultsets?

Solution 5:[5]

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

Solution 6:[6]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 FrankP
Solution 3 SchmitzIT
Solution 4 adolf garlic
Solution 5 MikeG
Solution 6 CEO

Related Questions