SQL Server Performance Forum – Threads Archive

Pls help URGENT!!

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

---

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/

---

]]>