Passing a null for a parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Passing a null for a parameter

Author

Topic

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-11-15 : 14:37:06

Is there a better way to have a parameter that is not provided by the user defualt to the wildcard "%".

In my case I am using this bit of code in my Where clause:

[CODE]
--NOTE: Any Part of the PO can be entered, or the entire PO number
(([T_SetupSheetHistoryHeader].CustPO) LIKE + N'%' +
(CASE
WHEN @CustomerPO IS NULL
THEN '%'
ELSE @CustomerPO
END)
+ N'%' ))
[/CODE]

Basically when the user presses a button on an Access form, I execute the stored procedure that this is in. The user is prompted for the date, a part number and the above CustomerPO. If they don't enter a PO then I don't want any one spacific record, meaning show me all the records that match the other paramters that they did enter.

When I run the sp from Query Anyalizer it runs in 5 seconds, when I run it from the Access .ADP directly it times out after 30 secoonds. I think it might have something to do with this null/wildcard ruteen.

Origninal sp:
[CODE](
@ReleaseDate nvarchar(20),
@SearchItem nvarchar(50),
@CustomerPO nvarchar(30)
)

AS
SELECT [T_SetupSheetHistoryHeader].WOID,
[T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum AS ItemID,
[T_SetupSheetHistoryCombinationsDetail].ComboColor AS OrdCol,
[T_SetupSheetHistoryCombinationsDetail].ComboColorActual AS Color,
[T_SetupSheetHistoryCombinationsDetail].ComboLength AS OrdLen,
[T_SetupSheetHistoryCombinationsDetail].ComboLengthActual AS Length,
[T_SetupSheetHistoryCombinationsDetail].ComboQty AS OrderQyt,
Sum([T_SetupSheetHistoryProduction].QtyGood) AS QtyPrdcd,
vShipping_WOIDItem_Balance_NOKIT.BalLng AS Balance,
[T_SetupSheetHistoryHeader].PartDescription,
[T_SetupSheetHistoryHeader].CustPO
FROM (([T_SetupSheetHistoryHeader]
INNER JOIN [T_SetupSheetHistoryCombinationsDetail]
ON ([T_SetupSheetHistoryHeader].ECN = [T_SetupSheetHistoryCombinationsDetail].ECN)
AND ([T_SetupSheetHistoryHeader].WOIDSub = [T_SetupSheetHistoryCombinationsDetail].WOIDSub)
AND ([T_SetupSheetHistoryHeader].WOID = [T_SetupSheetHistoryCombinationsDetail].WOID)
AND ([T_SetupSheetHistoryHeader].QuoteID = [T_SetupSheetHistoryCombinationsDetail].QuoteID))
LEFT JOIN vShipping_WOIDItem_Balance_NOKIT
ON ([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum = vShipping_WOIDItem_Balance_NOKIT.Item)
AND ([T_SetupSheetHistoryCombinationsDetail].WOID = vShipping_WOIDItem_Balance_NOKIT.WOID))
LEFT JOIN [T_SetupSheetHistoryProduction]
ON ([T_SetupSheetHistoryCombinationsDetail].ECN = [T_SetupSheetHistoryProduction].ECN)
AND ([T_SetupSheetHistoryCombinationsDetail].WOIDSub = [T_SetupSheetHistoryProduction].WOIDSub)
AND ([T_SetupSheetHistoryCombinationsDetail].WOID = [T_SetupSheetHistoryProduction].WONum)
AND ([T_SetupSheetHistoryCombinationsDetail].QuoteID = [T_SetupSheetHistoryProduction].QuoteID)
AND ([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum = [T_SetupSheetHistoryProduction].ComboCustPartNum)
WHERE ((([T_SetupSheetHistoryHeader].Revision)=0)
AND (([T_SetupSheetHistoryHeader].ReleasedDate)>@ReleaseDate))

--MOVED TO HERE
AND
((([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum) Like N'%' + @SearchItem + N'%')
AND (([T_SetupSheetHistoryHeader].CustPO) LIKE + N'%' +
(CASE
WHEN @CustomerPO IS NULL
THEN '%'
ELSE @CustomerPO
END) + N'%' ))

GROUP BY [T_SetupSheetHistoryHeader].WOID,
[T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum,
[T_SetupSheetHistoryCombinationsDetail].ComboColor,
[T_SetupSheetHistoryCombinationsDetail].ComboColorActual,
[T_SetupSheetHistoryCombinationsDetail].ComboLength,
[T_SetupSheetHistoryCombinationsDetail].ComboLengthActual,
[T_SetupSheetHistoryCombinationsDetail].ComboQty,
vShipping_WOIDItem_Balance_NOKIT.BalLng,
[T_SetupSheetHistoryHeader].PartDescription,
[T_SetupSheetHistoryHeader].CustPO,
[T_SetupSheetHistoryHeader].DueDate,
[T_SetupSheetHistoryHeader].WOID
/* MOVED FROM HERE
HAVING ((([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum) Like N'%' + @SearchItem + N'%')
AND (([T_SetupSheetHistoryHeader].CustPO) LIKE + N'%' + (CASE
WHEN @CustomerPO IS NULL
THEN '%'
ELSE @CustomerPO
END) + N'%' ))
*/
ORDER BY [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum,
[T_SetupSheetHistoryHeader].WOID[/CODE]

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

Kristen
Test

22859 Posts

Posted - 2005-11-15 : 15:15:04

You would be better off with

WHERE (@CustomerPO IS NULL OR [T_SetupSheetHistoryHeader].CustPO LIKE + N'%' + @CustomerPO)

than setting @CustomerPO to '%' and doing a LIKE against it, because that's a lot of serious CPU/disk work - whereas "@CustomerPO IS NULL" will just resolve to "TRUE" !

Kristen

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-11-17 : 14:05:49

I eded up testing for the null first, that seemed to work fine, with this code:

Select @CustomerPO= (CASE WHEN @CustomerPO IS NULL THEN '%' ELSE @CustomerPO END)

thanks.

(
@ReleaseDate nvarchar(20),
@SearchItem nvarchar(50),
@CustomerPO nvarchar(30)
)

AS
Select @CustomerPO= (CASE WHEN @CustomerPO IS NULL THEN '%' ELSE @CustomerPO END)

SELECT [T_SetupSheetHistoryHeader].WOID,
      [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum AS ItemID,
      [T_SetupSheetHistoryCombinationsDetail].ComboColor AS OrdCol,
      [T_SetupSheetHistoryCombinationsDetail].ComboColorActual AS Color,
      [T_SetupSheetHistoryCombinationsDetail].ComboLength AS OrdLen,
      [T_SetupSheetHistoryCombinationsDetail].ComboLengthActual AS Length,
      [T_SetupSheetHistoryCombinationsDetail].ComboQty AS OrderQyt,
      Sum([T_SetupSheetHistoryProduction].QtyGood) AS QtyPrdcd,
      vShipping_WOIDItem_Balance_NOKIT.BalLng AS Balance,
      [T_SetupSheetHistoryHeader].PartDescription,
      [T_SetupSheetHistoryHeader].CustPO
FROM (([T_SetupSheetHistoryHeader] 
INNER JOIN [T_SetupSheetHistoryCombinationsDetail] 
     ON ([T_SetupSheetHistoryHeader].ECN = [T_SetupSheetHistoryCombinationsDetail].ECN) 
     AND ([T_SetupSheetHistoryHeader].WOIDSub = [T_SetupSheetHistoryCombinationsDetail].WOIDSub) 
     AND ([T_SetupSheetHistoryHeader].WOID = [T_SetupSheetHistoryCombinationsDetail].WOID) 
     AND ([T_SetupSheetHistoryHeader].QuoteID = [T_SetupSheetHistoryCombinationsDetail].QuoteID)) 
LEFT JOIN vShipping_WOIDItem_Balance_NOKIT 
     ON ([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum = vShipping_WOIDItem_Balance_NOKIT.Item) 
     AND ([T_SetupSheetHistoryCombinationsDetail].WOID = vShipping_WOIDItem_Balance_NOKIT.WOID)) 
LEFT JOIN [T_SetupSheetHistoryProduction] 
     ON ([T_SetupSheetHistoryCombinationsDetail].ECN = [T_SetupSheetHistoryProduction].ECN) 
     AND ([T_SetupSheetHistoryCombinationsDetail].WOIDSub = [T_SetupSheetHistoryProduction].WOIDSub) 
     AND ([T_SetupSheetHistoryCombinationsDetail].WOID = [T_SetupSheetHistoryProduction].WONum) 
     AND ([T_SetupSheetHistoryCombinationsDetail].QuoteID = [T_SetupSheetHistoryProduction].QuoteID) 
     AND ([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum = [T_SetupSheetHistoryProduction].ComboCustPartNum)
WHERE ((([T_SetupSheetHistoryHeader].Revision)=0) 
     AND (([T_SetupSheetHistoryHeader].ReleasedDate)>@ReleaseDate))
GROUP BY [T_SetupSheetHistoryHeader].WOID,
      [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum,
      [T_SetupSheetHistoryCombinationsDetail].ComboColor,
      [T_SetupSheetHistoryCombinationsDetail].ComboColorActual,
      [T_SetupSheetHistoryCombinationsDetail].ComboLength,
      [T_SetupSheetHistoryCombinationsDetail].ComboLengthActual,
      [T_SetupSheetHistoryCombinationsDetail].ComboQty,
      vShipping_WOIDItem_Balance_NOKIT.BalLng,
      [T_SetupSheetHistoryHeader].PartDescription,
      [T_SetupSheetHistoryHeader].CustPO,
      [T_SetupSheetHistoryHeader].DueDate,
      [T_SetupSheetHistoryHeader].WOID
HAVING [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum Like N'%' + @SearchItem + N'%'
     AND  (dbo.T_SetupSheetHistoryHeader.CustPO LIKE + N'%' +  @CustomerPO + N'%')
ORDER BY [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum,
      [T_SetupSheetHistoryHeader].WOID

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:12:41

Well that isn't actually what I meant. You are enforcing a wildcard test, when one is not needed.

Using:

@MyParameter IS NULL OR MyColumn LIKE '%SomeWildCard%'

in the WHERE / HAVING clause gives the query planner the option to completely bypass the wildcard test when "@MyParameter IS NULL". Otherwise SQL has to thumb through every single record in the table, without using any indexes, to make a test that every record is going to pass.

Actually not quite true - your test will NOT match records where T_SetupSheetHistoryHeader.CustPO IS NULL, whereas mine will [dunno if that's important, and of course its possible that that particular column is not nullable]

Kristen

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 14:42:25

quote:
Originally posted by Kristen

Well that isn't actually what I meant. You are enforcing a wildcard test, when one is not needed.

Using:

@MyParameter IS NULL OR MyColumn LIKE '%SomeWildCard%'

in the WHERE / HAVING clause gives the query planner the option to completely bypass the wildcard test when "@MyParameter IS NULL". Otherwise SQL has to thumb through every single record in the table, without using any indexes, to make a test that every record is going to pass.

Actually not quite true - your test will NOT match records where T_SetupSheetHistoryHeader.CustPO IS NULL, whereas mine will [dunno if that's important, and of course its possible that that particular column is not nullable]

Kristen

Many times I see where putting the parameter first (left side of =..etc) would force a table scan ... Is that the case on a parm null test?

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-11-17 : 14:44:39

Here is my issue:
1. The user will always supply a date
2. The user will supply the SearchItem
A. exactly
B. partially
c. not at all
3. The user will supply the CustomerPO
A. exactly
B. partially
c. not at all

So, I guess my question should be: "How to do that?" I am open for suggestions, I thought I was doing that with my latest code, but if it can be improved upon, I am all ears (green as they are.) ;-)

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 15:05:14

We have the same dilemma - users want complete freedom for searching (fair enough), we want the queries to run in a manner that doesn't will the server (which translates to "as quickly as possible" and the user wants that too, so we all win).

For "not at all" we want to exclude that test altogether - i.e. match everything.

For "exactly" we would prefer to use an EQUALS test so that query planner can use a direct-hit index, if one is available.

And for "partially" we are stuck with LIKE.

By the by, I have never understood why SQL Server did not optimise:

MyColumn LIKE 'ABC'
to
MyColumn = 'ABC'

i.e. if the right-hand-bit doesn't actually contain wildcards then do equality rather than wildcard.

Using
WHERE (@MyParameter IS NULL OR MyColumn LIKE @MyParameter)
gives the optimiser a chance to equate that test to TRUE in cases where @MyParameter is null. (I dunno whether it actually does, sometime does, or never manages it, but the chance is there).

The only other alternative I can think of is to use sp_ExecuteSQL and some dynamic SQL:

SELECT strSQL = 'SELECT MyCol1, MyCol2 FROM MyTable WHERE 1=1 '
IF @MyParameter1 IS NOT NULL
BEGIN
SELECT strSQL = strSQL + 'AND MyColumn1 LIKE @MyParameter1'
END

IF @MyParameter2 IS NOT NULL
BEGIN
SELECT strSQL = strSQL + 'AND MyColumn2 LIKE @MyParameter2'
END

EXEC sp_ExecuteSQL strSQL,
'@MyParameter1 varchar(10), @MyParameter2 varchar(20)',
@MyParameter1, @MyParameter2
[/code]
Note that the optimiser will "optimsie out" parameters where, e.g., @MyParameter1 is defined and provided as a parameter, but not actually be used in [this particular] query.

This route also gives you the opportunity to change LIKE to "=" if there are no wildcards in the comparison string, and to not prefix "%" if the user has appended "%" - so you wind up with a smarter query where the user is skilled enough to indicate that a "Starts with" test will do - and then any approrpiate index will get used.

Downside is that the USER (rather than the creator of the SProc) MUST have SELECT permissions on the table(s)

Upside is that parameterised queries to sp_ExecuteSQL are cached (only if they reoccur, but that is likely, least-ways for the "popular variations"

Having said "The only other alternative ..." we do use another approach, but quite rarely. That is to build a temporary table and populate it with PKs. We do that for each parameter in turn, refining the list.

So we might say:

"If they give us a PostCode we'll use that first, then refine on name and telephone number"

"Otherwise if they give us a telephone number we'll select on that, and then refine on Name"

We have a "find" screen that allows call centre staff to Find based on shedloads of possible criteria relating to customers. We could provide them with a screen with specific boxes, but we don't - they just have a single field that they can type in name, postcode, telephone number, email address, etc. We use a temporary table approach to narrow the search, and it works very well. And certainly just doing a "wildcard search on all 10 possible fields" is desperately slower by comparison.

Kristen

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-11-17 : 15:46:30

So am I understanding it correctly that you are suggesting to re-write:

HAVING [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum Like N'%' + @SearchItem + N'%'
     AND  (dbo.T_SetupSheetHistoryHeader.CustPO LIKE + N'%' +  @CustomerPO + N'%')

HAVING (@SearchItem IS NULL OR [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum LIKE + N'%' + @SearchItem )
     AND  (@CustomerPO IS NULL OR [T_SetupSheetHistoryHeader].CustPO LIKE + N'%' + @CustomerPO)

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 00:13:51

Yup, that's the one! (But couldn't that be in the WHERE clause, rather than HAVING?)

No guarantee that the optimiser will bypass that when the parameter is NULL, but its worth a test of what query plan is generated when one or other of your two parameters is NULL

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources