October 22, 2009 at 9:27 am
I like to use something like the following:
DECLARE @P_Overzicht VARCHAR(250)
SET @P_Overzicht = ''
DECLARE @P_Afdeling VARCHAR(250)
SET@P_Afdeling = 'Managed Server Environment'
DECLARE @P_Type VARCHAR(250)
SET@P_Type = 'Geregistreerd'
If @P_Type='Totaal'Set @P_Overzicht=''
If @P_Type='Geregistreerd'Set @P_Overzicht='incident.afhandelingstatusid'
select
incident.naamas Incidentnr,
vestiging.naamas Klant,
incident.korteomschrijvingas Korte_Omschrijving,
incident.datumaangemeldas Datum_Aanmelden,
incident.datumafspraakslaas Datum_SLA,
afhandelingstatus.naamas Status_Incident,
dnodienst.omschrijvingas ServiceLevel,
incident.ref_soortmeldingas Soort_Melding,
soortbinnenkomst.naamas Binnenkomst
from
incident with (NOLOCK)
inner join soortmeldingwith (NOLOCK) on incident.soortmeldingid=soortmelding.unid
inner join dnodienstwith (NOLOCK) on incident.ref_dnodienstid = dnodienst.unid
inner join soortbinnenkomstwith (NOLOCK) on incident.soortbinnenkomstid = soortbinnenkomst.unid
inner join vestigingwith (NOLOCK) on incident.aanmeldervestigingid = vestiging.unid
inner join afhandelingstatuswith (NOLOCK) on incident.afhandelingstatusid = afhandelingstatus.unid
where
incident.afgemeld='0'
and incident.gereed='0'
and soortmelding.naam=@P_Afdeling
and @P_Overzicht=CASE WHEN @P_Type='Geregistreerd' THEN '4N0001' ELSE '4N0002' END
order by
incident.naam
When I use this, I've got 0 results.
When I change
and @P_Overzicht=@P_Overzicht=CASE WHEN @P_Type='Geregistreerd' THEN '4N0001' ELSE '4N0002' END
with
and incident.afhandelingstatusid=CASE WHEN @P_Type='Geregistreerd' THEN '4N0001' ELSE '4N0002' END
I've got 5 results.
So it looks like the @P_Overzicht is not translated in a WHERE statement.
Can I solve this?
October 22, 2009 at 9:35 am
The problem is you can't use a variable to define a column name like that. You could do something like build an SQL string and then use the exec () command to dynamically build this but I don't think that should be required for this.
What are trying to accomplish with that where clause?
October 22, 2009 at 12:32 pm
Hi, thx for the reply.
This query has to function in a dataset from a subreport in Reporting Services.
By passing different parameter values I would like to use 1 report with 1 query to get different results.
October 22, 2009 at 2:12 pm
htilburgs (10/22/2009)
I like to use something like the following:
DECLARE @P_Overzicht VARCHAR(250)
SET @P_Overzicht = ''
DECLARE @P_Afdeling VARCHAR(250)
SET@P_Afdeling = 'Managed Server Environment'
DECLARE @P_Type VARCHAR(250)
SET@P_Type = 'Geregistreerd'
If @P_Type='Totaal'Set @P_Overzicht=''
If @P_Type='Geregistreerd'Set @P_Overzicht='incident.afhandelingstatusid'
select
incident.naamas Incidentnr,
vestiging.naamas Klant,
incident.korteomschrijvingas Korte_Omschrijving,
incident.datumaangemeldas Datum_Aanmelden,
incident.datumafspraakslaas Datum_SLA,
afhandelingstatus.naamas Status_Incident,
dnodienst.omschrijvingas ServiceLevel,
incident.ref_soortmeldingas Soort_Melding,
soortbinnenkomst.naamas Binnenkomst
from
incident with (NOLOCK)
inner join soortmeldingwith (NOLOCK) on incident.soortmeldingid=soortmelding.unid
inner join dnodienstwith (NOLOCK) on incident.ref_dnodienstid = dnodienst.unid
inner join soortbinnenkomstwith (NOLOCK) on incident.soortbinnenkomstid = soortbinnenkomst.unid
inner join vestigingwith (NOLOCK) on incident.aanmeldervestigingid = vestiging.unid
inner join afhandelingstatuswith (NOLOCK) on incident.afhandelingstatusid = afhandelingstatus.unid
where
incident.afgemeld='0'
and incident.gereed='0'
and soortmelding.naam=@P_Afdeling
and @P_Overzicht=CASE WHEN @P_Type='Geregistreerd' THEN '4N0001' ELSE '4N0002' END
order by
incident.naam
When I use this, I've got 0 results.
When I change
and @P_Overzicht=@P_Overzicht=CASE WHEN @P_Type='Geregistreerd' THEN '4N0001' ELSE '4N0002' END
with
and incident.afhandelingstatusid=CASE WHEN @P_Type='Geregistreerd' THEN '4N0001' ELSE '4N0002' END
I've got 5 results.
So it looks like the @P_Overzicht is not translated in a WHERE statement.
Can I solve this?
What should the statement look like if @P_type is passed as 'Totaal'? It looks like then you would be saying:
and '' = CASE WHEN @P_Type='Geregistreerd' THEN '4N0001' ELSE '4N0002' END
edit: sorry this took me a while to get back to. I got caught up in some other stuff this afternoon.
October 24, 2009 at 1:44 am
Ok....
I've solved the problem on a different way.
Now I use the next code:
If @P_Type = 'Totaal' Goto Totaal
If @P_Type = 'Geregistreerd' Goto Geregistreerd
Totaal:
select .......
GOTO EINDE
Geregistreerd:
select ......
GOTO EINDE
EINDE:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply