Report SQL with temp table in it.

[David]

Hi,

I'm trying to use the following query with a SqlDataSource in a report. The query works fine if I run it from Sql Server Management Studio. I get an error, see attached image.

SQL:

declare @Temp Table
(
    Namn varchar(100),
    Beskrivning varchar(100),
    Grupp varchar(100)
)
insert into @Temp(Namn, Beskrivning, Grupp) VALUES('41', 'Material', '41-42')
insert into @Temp(Namn, Beskrivning, Grupp) VALUES('42', 'Material', '41-42')
insert into @Temp(Namn, Beskrivning, Grupp) VALUES('43', 'UE', '43-44')
insert into @Temp(Namn, Beskrivning, Grupp) VALUES('44', 'UE', '43-44')
insert into @Temp(Namn, Beskrivning, Grupp) VALUES('45', 'Installationer', '45')

SELECT kgt.Namn AS KontoNr, kgt.Beskrivning AS Typ, kgt.Grupp as Kontor, k.Privat,
SUM(CASE LEFT(ko.KontoNr, 1) WHEN '3' THEN 0 WHEN '9' THEN 0 ELSE tb.Belopp END) AS [Sum], 
CASE WHEN k.PrivatPerson = 1 THEN 'Konsument' ELSE isnull(k.Namn,'Kund saknas') END AS kundnamn
FROM            Table1 tb
LEFT OUTER JOIN Table2 AS ko ON tb.KontoId = ko.Id
LEFT OUTER JOIN @KontoGruppTemp kgt on
case 
    when LEN(kgt.Namn) = 3 
then CONVERT(INT, (LEFT(ko.KontoNr, 3)))
else
CONVERT(INT, (LEFT(ko.KontoNr, 2)))
end
= kgt.Namn
LEFT OUTER JOIN Aktivitet AS a ON tb.AktivitetId = a.Id
LEFT OUTER JOIN AktivitetKund AS ak ON a.Id = ak.AktivitetId AND ak.ValdUppfoljning = 1
LEFT OUTER JOIN Kund AS k ON ak.KundId = k.Id
LEFT OUTER JOIN KundForetag AS kf ON k.Id = kf.KundId
LEFT OUTER JOIN Projekt p on p.Id = tb.ProjektId
WHERE a.ProjektId IN (@ProjektId)

        AND kgt.Beskrivning IS NOT NULL
        AND tb.Datum >= @FranDatum

        AND tb.Datum <= @TillDatum

        AND p.BasenStatus IN(1,2)
GROUP BY    k.Namn, kgt.Namn, kgt.Beskrivning, k.PrivatPerson, kgt.Grupp
order by kgt.Namn, kgt.Grupp

 

Any ideas 

[David]

Forgot to attach image.

[David]

Attachment

[David]

Unable to attach image. However, the message I get is:

The variable name '@Temp' has already been declared. Varible names must be unique within a query batch or stored procedure.

[Stef]

Hi David,

Please check How to configure Stored Procedure with Temporary Tables for use with SqlDataSource component.

Regards,
Stef
Telerik by Progress