Use PARAMETER in WHERE statement

  • 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?

  • 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?

  • 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.

  • 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.

  • 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