Conditional Where clause

[olichap]

Hello,

I've a query that can accept 6 variables that affect it's outcome. Currently the SQL string is built dynamically through an asp. So if the user selects values for COUNTRY and PRODUCT the string is built to look something like:

....where ProductID = @varProductID and CountryCode = @varCountryCode

I'm trying to create a stored procedure that will be able to handle all the possible variations in the WHERE clause but having some trouble.

I've tried something like:

declare @iCnty int, @iProd int

if @varProductID > 0 set @iprod = 1 else set @iprod = 0
if @varCountryCode > 0 set @iCnty = 1 else set @iCnty = 0

Then in the WHERE:

WHERE
((@iProd = 1 and ProductID = @varProductID) or (@iProd = 0 and ProductID > 0))
and
((@iCnty = 1 and CountryCode = @varCountryCode ) or (@iCnty = 0 and CountryCode > 0))

So if the user populates both ProductID and CountryCode I would expect the query to evaluate both "@var.." variables given the presence of @iprod/@icnty = 1.

If only @varProductID is populated I would expect it to return all records with this ProductID. The lack of a @varCountryCode should have no affect over the results; should be as if it wasn't part of the where clause at all.

I can get this logic to work if I'm only dealing with one variable but as soon as I add the second my query just seems to run for ever. I've not had a chance to see whether it would ever finish (when passed only a @varProductID) but it should only take 3 seconds to run.

Hopefully I explained this well enough. Thanks in advance for any input and assistance.

O.

 

[gmmastros]

Something like this may works.

Create Procedure GetSomeData
  @ProductId Integer NULL,
  @CountryCode VarChar(100) NULL
As
SET NOCOUNT ON

Select Field1, Field2
From   Table
Where  ProductId = Case When IsNull(@ProductId, 0) = 0
                        Then ProductId
                        Else @ProductId
                        End
       And CountryCode = Case When IsNull(@CountryCode, '') = ''
                              Then CountryCode
                              Else @CountryCode
                              End

Of course, you'll need to extend this for the other 4 fields you need, but the process should be clear enough for you to continue.

Let us know how you make out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

You logic seems to be correct. I write many queries the same way. It could be the way you are testing and you are pulling back a lot of rows.

 

[JamesLean]

The method I usually use is:

CREATE PROC procname
  @product int = NULL,
  @country int = NULL
AS

SELECT cols
FROM table
WHERE (@product IS NULL OR product = @product)
  AND (@country IS NULL OR country = @country)
  -- etc...

--James

 

[olichap]

Thanks all,

The code I have (that runs but doesn't seem to finish) looks like this:
--Sample1
and
(@iLic = 1 and lc.LicenseCompanyID = @LicenseCompanyID or @iLic = 0)
and
(@iPT = 1 and p.refProductTypeID = @ProductType or @ipt = 0)

I've also tried:
--Sample2
and
(@iLic = 1 and lc.LicenseCompanyID = @LicenseCompanyID or @iLic = 0 and lc.licenseCompanyID > 0)
and
(@iPT = 1 and p.refProductTypeID = @ProductType or @ipt = 0 and p.refProductTypeid > 0)

In Sample1 when I run with the @iPT code commented-out my query returns 62 records in about a second. With it un-commented it keeps running for an indeterminant period.

In Sample2 the query simply keeps running. I've not had a chance to see for how long it'd go.

It seems to me that either should work. If the user doesn't select a CompanyID but does a ProductID the query should return the proper values (and hopefully not take three days to run). I feel like I'm missing something here.

O.

 

[LFCfan]

I'd guess you're mssing some brackets - ie you're not separating the logic:

and
([red]([/red]@iLic = 1 and lc.LicenseCompanyID = @LicenseCompanyID[red])[/red] or @iLic = 0)
and
([red]([/red]@iPT = 1 and p.refProductTypeID = @ProductType[red])[/red] or @ipt = 0)


_{"In vain have I struggled. It will not do."}

 

[olichap]

Yeah. Sorry, I forgot to paste in the code after changing and re-adding the brackets.

and
((@iLic = 1 and lc.LicenseCompanyID = @LicenseCompanyID) or (@iLic = 0))-- and lc.licensecompanyID > 0))
and
((@iPT = 1 and p.refProductTypeID = @ProductType) or (@ipt = 0)) -- and p.refProductTypeID > 0)

This time I let the test run and it did complete; did return the same number of rows as my earlier test with only part of the conditional active. However it took over 6 1/2 minutes instead of 1-3 seconds.

 

[Catadmin]

Check your Estimated Execution Plan (It's in QA under the QUERY menu). Look for Table Scans and Index Scans. It'll tell you what part of your query is taking the longest. Once you know that, we can help you tune that part to speed up the process.

You'll probably need to add Hints.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???