Stored Procedure Parameters with LIKE expression

Question

Post reply

Stored Procedure Parameters with LIKE expression

duanecwilson

Ten Centuries

Points: 1057
More actions
March 18, 2010 at 10:45 am

#230980

I have this SP that works fine and allows me to enter any of the parameters and returns a sub-set of the data based on that parameter or parameters. The only problem is that if I enter a partial string, the LIKE expressions return nothing. I would like to use this partial match. For example, one of the AppNames would be 'Adobe Acrobat Professional'
If I enter:
EXEC sp_ReportBasic @AppName = 'Adobe Acrobat Professional', I get some records.
However, if I enter any of the following, I get nothing:
EXEC sp_ReportBasic @AppName = 'Adobe', I get some records.
EXEC sp_ReportBasic @AppName = 'Acrobat', I get some records.
EXEC sp_ReportBasic @AppName = 'Professional', I get some records.
If you can help me figure it out, I would appreciate it.
Here is the stored procedure:
CREATE PROCEDURE dbo.sp_ReportBasic
@AppName VARCHAR(max) = NULL,
@WkstnName VARCHAR(128) = NULL,
@EmployeeID VARCHAR(255) = NULL,
@PAYROLL_CO_num CHAR(4) = NULL,
@PAYROLL_RC_num CHAR(7) = NULL
AS
BEGIN
SELECTv_CommonApp.AppName
, v_SMSAppData.[Version]
, v_SMSAppData.WkstnName
, SMS_Hardware.EmployeeID
, ePeople_feed.NAME
, ePeople_feed.PAYROLL_CO_num
, ePeople_feed.PAYROLL_RC_num
, ePeople_feed.EMAIL_ADDRESS
, ePeople_feed.PHONE_NUMBER
FROM v_SMSAppData
LEFT JOIN v_CommonApp ON v_SMSAppData.appID = v_CommonApp.AppID
LEFT JOIN SMS_Hardware ON v_SMSAppData.WkstnName = SMS_Hardware.wksname
LEFT JOIN ePeople_feed ON SMS_Hardware.EmployeeID = ePeople_feed.EMPLOYEE_ID
WHERE AppName LIKE COALESCE(@AppName, '%')
AND WkstnName LIKE COALESCE(@WkstnName, '%')
AND EmployeeID LIKE COALESCE(@EmployeeID, '%')
AND PAYROLL_CO_num LIKE COALESCE(@PAYROLL_CO_num, '%')
AND PAYROLL_RC_num LIKE COALESCE(@PAYROLL_RC_num, '%')
ORDER BY v_CommonApp.AppName
END
GOThank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

roger.price-1150775 Default port Points: 1458 More actions · Answer 1

Change this:

WHERE AppName LIKE COALESCE(@AppName, '%')

to this:

WHERE AppName LIKE '%'+@AppName+'%'

duanecwilson Ten Centuries Points: 1057 More actions · Answer 2

Thank you. I forgot about appending the '%' to the parameter.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

You will find yourself getting a LOT of table scans using that approach. It is almost unavoidable given that you are going to be searching for strings LIKE '%Adobe%'.

You might want to do some research into full-text indexing, which basically lets you build an index over each word in the column(s) you are searching. As your table grows, you will need the performance boost.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 4

The Dixie Flatline (3/18/2010)
You might want to do some research into full-text indexing

Absolutely, yes. LIKE is not the way forward here. Full-text is easy, fast, and free! Why re-invent it?

As it happens, I just posted a small demo for a similar problem over in the SS2K5 forum:

http://www.sqlservercentral.com/Forums/Topic886078-338-1.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi