This is a migrated thread and some comments may be shown as answers.

Report SQL with temp table in it.

4 Answers 191 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
David
Top achievements
Rank 1
David asked on 28 Mar 2017, 01:34 PM

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 

4 Answers, 1 is accepted

Sort by
0
David
Top achievements
Rank 1
answered on 28 Mar 2017, 01:35 PM
Forgot to attach image.
0
David
Top achievements
Rank 1
answered on 28 Mar 2017, 01:35 PM
Attachment
0
David
Top achievements
Rank 1
answered on 28 Mar 2017, 01:38 PM

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.

0
Accepted
Stef
Telerik team
answered on 28 Mar 2017, 01:43 PM
Hi David,

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

Regards,
Stef
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Tags
General Discussions
Asked by
David
Top achievements
Rank 1
Answers by
David
Top achievements
Rank 1
Stef
Telerik team
Share this question
or