SQL Server Performance

Pls help URGENT!!

Discussion in 'SQL Server 2005 Reporting Services' started by sha_shivani, Apr 23, 2007.

  1. sha_shivani New Member


    How do i select all the processes from my dropdown?In my dropdown i have three options All,Forclosure,Bancrupcy.How do i select All so that it should display both forclosure and bancrupcy processes.Below is the query i am uing.





    declare @Attorneyrname as string
    declare @Report_Parameter_1 as string


    SELECT DISTINCT
    dbo.tblVENDOR.VendorName, dbo.tblVENDOR.VendorShortName, dbo.tblRAIL.RailDescr, dbo.tblSPIKE.SpikeDescr, dbo.tblRAIL.ScreenValue,
    dbo.tlkpSID.SId, dbo.tblConVendtoSuperClient.VendorID
    FROM dbo.tlkpSID INNER JOIN
    dbo.tblSPIKE ON dbo.tlkpSID.SidID = dbo.tblSPIKE.SidId INNER JOIN
    dbo.tblRAIL INNER JOIN
    dbo.tblVENDOR ON dbo.tblRAIL.SuperClientVendorId = dbo.tblVENDOR.VendorId ON dbo.tblSPIKE.RailId = dbo.tblRAIL.RailId INNER JOIN
    dbo.tblConVendtoSuperClient ON dbo.tblVENDOR.VendorId = dbo.tblConVendtoSuperClient.SuperClientId
    WHERE (dbo.tblConVendtoSuperClient.VendorID = 199) AND (dbo.tblvendor.vendorid <> 123)
    and (dbo.tblVENDOR.VendorName = @Attorneyrname) and
    dbo.tblRAIL.lkpRailid = CASE @Report_Parameter_1
    WHEN 'Forclosure' THEN 1
    WHEN 'Bankrupcy' THEN 2
    END

  2. ndinakar Member

    If its 'ALL', the perhaps you want everything where lkpRailid = 1 and lkpRailid = 2 ?
    you could change the query to



    AND dbo.tblRAIL.lkpRailid IN (CASE @Report_Parameter_1 WHEN 'Forclosure' THEN ( 1 ) WHEN 'Bankrupcy' THEN ( 2 ) ELSE (1,2) END


    but i think this will be faster:



    IF @Report_Parameter_1 = 'ALL'
    BEGIN
    SELECT DISTINCT
    dbo.tblVENDOR.VendorName, dbo.tblVENDOR.VendorShortName, dbo.tblRAIL.RailDescr, dbo.tblSPIKE.SpikeDescr, dbo.tblRAIL.ScreenValue,
    dbo.tlkpSID.SId, dbo.tblConVendtoSuperClient.VendorID
    FROM dbo.tlkpSID INNER JOIN
    dbo.tblSPIKE ON dbo.tlkpSID.SidID = dbo.tblSPIKE.SidId INNER JOIN
    dbo.tblRAIL INNER JOIN
    dbo.tblVENDOR ON dbo.tblRAIL.SuperClientVendorId = dbo.tblVENDOR.VendorId ON dbo.tblSPIKE.RailId = dbo.tblRAIL.RailId INNER JOIN
    dbo.tblConVendtoSuperClient ON dbo.tblVENDOR.VendorId = dbo.tblConVendtoSuperClient.SuperClientId
    WHERE (dbo.tblConVendtoSuperClient.VendorID = 199) AND (dbo.tblvendor.vendorid <> 123)
    and (dbo.tblVENDOR.VendorName = @Attorneyrname)

    END
    ELSE
    BEGIN
    SELECT DISTINCT
    dbo.tblVENDOR.VendorName, dbo.tblVENDOR.VendorShortName, dbo.tblRAIL.RailDescr, dbo.tblSPIKE.SpikeDescr, dbo.tblRAIL.ScreenValue,
    dbo.tlkpSID.SId, dbo.tblConVendtoSuperClient.VendorID
    FROM dbo.tlkpSID INNER JOIN
    dbo.tblSPIKE ON dbo.tlkpSID.SidID = dbo.tblSPIKE.SidId INNER JOIN
    dbo.tblRAIL INNER JOIN
    dbo.tblVENDOR ON dbo.tblRAIL.SuperClientVendorId = dbo.tblVENDOR.VendorId ON dbo.tblSPIKE.RailId = dbo.tblRAIL.RailId INNER JOIN
    dbo.tblConVendtoSuperClient ON dbo.tblVENDOR.VendorId = dbo.tblConVendtoSuperClient.SuperClientId
    WHERE (dbo.tblConVendtoSuperClient.VendorID = 199) AND (dbo.tblvendor.vendorid <> 123)
    and (dbo.tblVENDOR.VendorName = @Attorneyrname) and
    dbo.tblRAIL.lkpRailid = CASE @Report_Parameter_1
    WHEN 'Forclosure' THEN 1
    WHEN 'Bankrupcy' THEN 2
    END
    END



    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/

Share This Page