AND
1=CASE
WHEN (@PARTY_ID IS NULL) THEN 1
ELSE 0
END

(@CASE_NUMBER IS NULL OR CASE_NUMBER = @CASE_NUMBER)
AND ...

quote:

---

Originally posted by Kristen

Well, being picky!", but this looks bad to me:

AND
1=CASE
WHEN (@PARTY_ID IS NULL) THEN 1
ELSE 0
END

why not just do

AND @PARTY_ID IS NULL

note that there is a strong chance that the Optimiser will not handle the CASE as efficiently.

and this:

(ISNULL(CASE_NUMBER, 0) = ISNULL(@CASE_NUMBER, ISNULL(CASE_NUMBER, 0)))

is bad because if CASE_NUMBER is indexed all that encapsulating with function calls will mean that the Optimiser can't use any indexes, and will scan the whole table instead, potentially with disastrous performance results.

For these sorts of "variable parameters" I reckon the best bet is:

(@CASE_NUMBER IS NULL OR CASE_NUMBER = @CASE_NUMBER)
AND ...

(Note that the IS NULL test comes first, as that is "cheaper" than actually checking the index for "CASE_NUMBER = NULL" ) this works if the user provides a parameter for @CASE_NUMBER only matching records are retrieved, and if the user provides NULL for this parameter then ALL records match [for this criteria].

Only downside I am aware of is that there is no way to explicitly match NULL values in this column; but that's never been an issue for my users, either they want a specific value, or "anything" (but never "Blanks only")

This is an OK means of wild-card matching, but the optimiser may still not do a brilliant job.

So if performance is still rubbish then there are two further routes that I use:

First is to pre-select PKs into a temporary table depending on what parameters are provided. So if you have, say, a Name Search function that lets you search on Name, ZipCode and Telephone Number you may decide that the telephone number is the "best" match - only likely to yield a few matches; ZipCode is next, and finally Name (because lets assume it will use a wild-card match, which is slow).

So you can do:

IF @Phone IS NOT NULL
BEGIN
... Get PKs matching @Phone into #TEMP
END
ELSE
IF @ZipCode IS NOT NULL
BEGIN
... Get PKs matching @ZipCode into #TEMP
END
ELSE
BEGIN
... Get PKs for records with Name LIKE '%' + @Name + '%' into #TEMP
END

... then ...

SELECT Col1, Col2, ...
FROM #TEMP AS T
JOIN MyAddressTable AS A
ON A.MyPK = T.MyPK
WHERE
(@Phone IS NULL OR MyPhone = @Phone)
AND (@ZipCode IS NULL OR MyZipCode = @ZipCode)
AND (@Name IS NULL OR Name LIKE '%' + @Name + '%')
ORDER BY ...

the idea being that the first-pass reduces the number of rows tested in the second pass. (Technically you don't need to compare @Phone in the second pass because if it was specified it would have been filtered in the first pass)

Beyond this you can look at dynamic SQL:

Using something like VB for the string handling:

DIM strSQL ' Build syntax for SQL command

srtSQL = 'SELECT Col1, Col2, ... '
srtSQL = srtSQL & 'FROM MyAddressTable '
srtSQL = srtSQL & 'WHERE 1=1 '
IF FormFieldPhone <> "" Then srtSQL = srtSQL & 'AND MyPhone = @Phone '
IF FormZipCode <> "" Then srtSQL = srtSQL & 'AND MyZipCode = @ZipCode '
IF FormName <> "" Then srtSQL = srtSQL & 'AND Name LIKE '%' + @Name + '%'
srtSQL = srtSQL & 'ORDER BY ...'

' Prepare SQL Execution command:
srtSQL = "sp_ExecuteSQL N'" & srtSQL & "', " _
    & "'@Phone varchar(50), @ZipCode varchar(10), @Name varchar(50)', " _
    & "@ZipCode = '" & FormFieldPhone & "', "
    & "@Phone = '" & FormZipCode & "', "
    & "@Name = '" & FormName & "' "

then you need to execute strSQL with whatever connection method you use. strSQL will contain something like this:

sp_ExecuteSQL N'SELECT Col1, Col2, ... 
    FROM MyAddressTable 
    WHERE 1=1 
        AND MyZipCode = @ZipCode
    ORDER BY ...',
    '@Phone varchar(50), @ZipCode varchar(10), @Name varchar(50)',
    @ZipCode = '1234',
    @Phone = '',
    @Name = ''
[/code]
Notice that only @ZipCode is used, representing the single form field parameter that the user has provided, and the VB has only included ZipCode in the WHERE clause, but there is no need to suppress parameters for the other fields that might have been potentially included.

The Query Optimiser will cache the query for ZipCode (Note that the actual ZipCode does NOT appear in the WHERE clause, it is provided as the parameter @ZipCode instead, and therefore ALL ZipCode queries will send this EXACT same syntax, and thus match the cached query plan, which will be reused.)

If someone else searches for Name, or Phone, that won't (yet) be in the cached query plan, so a new query plan will be built , and cached.  Thus popular variations of this query will all be cached, and the unpopular ones - Tough! - the user will have to wait a bit longer to get their results.

BUT ... because the WHERE clause is now so "slim" there is a much better chance that Indexes get used and so on, so even for "unpopular" queries the results are still likely to be way faster than the "wildcard SProc" discussed earlier.

Another couple of things:

CREATE PROCEDURE SP_RPT

don't start your Sproc names with "sp_", SQL server treats these names a special way, and the result is inefficient.

FROM [RPT_V_DISPJUDGEMENT]

Always include the Owner Name, to give the maximum chance that your query will be cached. So you should use:

FROM dbo.[RPT_V_DISPJUDGEMENT]

otherwise there is a risk that the system will first search for a table, and cached query plan, for [YourLoginName].[RPT_V_DISPJUDGEMENT], which it won't find, and then repeat the process to look for "dbo.xxx"

Kristen