Stored Procedure Parameters with LIKE

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 2005 Forums

Transact-SQL (2005)

Stored Procedure Parameters with LIKE

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-18 : 12:49:11

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
SELECT	v_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
GO

Thank you.

Duane

Kristen
Test

22859 Posts

Posted - 2010-03-18 : 12:51:06

[code]
WHERE AppName LIKE COALESCE('%' + @AppName + '%', '%')
[/code]
However, you SProc is hugely inefficient. This would be more efficient:
[code]
WHERE (@AppName IS NULL OR AppName LIKE @AppName)
AND (@WkstnName IS NULL OR WkstnName LIKE @WkstnName)
AND (@EmployeeID IS NULL OR EmployeeID LIKE @EmployeeID)
AND (@PAYROLL_CO_num IS NULL OR PAYROLL_CO_num LIKE @PAYROLL_CO_num)
AND (@PAYROLL_RC_num IS NULL OR PAYROLL_RC_num LIKE @PAYROLL_RC_num)
[/code]

Kristen
Test

22859 Posts

Posted - 2010-03-18 : 12:56:26

Also:

@AppName VARCHAR(max) - do you really have Apoplication names that are more than 8,000 characters? varchar(MAX) has inefficiencies compared to varchar(nnnn) - use an appropraite limit whenever possible

@PAYROLL_CO_num CHAR(4)
@PAYROLL_RC_num CHAR(7)

These have "num" in the name, are these parameters numbers? best not to use CHAR data type, and if the PAYROLL_CO_num is actually a numeric column then using "LIKE" will be very inefficient, and probably not deliver what you want. The use of CHAR may also be problematic as it is space-padded - so you may not be getting the wildcard matching you are expecting.
AS

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-18 : 12:59:26

Thank you. That worked like a charm. I am surprised I didn't catch that myself.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-18 : 13:03:10

quote:
Originally posted by Kristen

Also:

@AppName VARCHAR(max) - do you really have Apoplication names that are more than 8,000 characters? varchar(MAX) has inefficiencies compared to varchar(nnnn) - use an appropraite limit whenever possible

@PAYROLL_CO_num CHAR(4)
@PAYROLL_RC_num CHAR(7)

These have "num" in the name, are these parameters numbers? best not to use CHAR data type, and if the PAYROLL_CO_num is actually a numeric column then using "LIKE" will be very inefficient, and probably not deliver what you want. The use of CHAR may also be problematic as it is space-padded - so you may not be getting the wildcard matching you are expecting.
AS

Thank you for this reply as well. I was only using the datatype that was in the table already. Does that matter? Also, I was thinking about doing exact matches for the PAYROLL_CO_num and the PAYROLL_RC_num. Do you have to match datatypes and lengths in the parameters?

Duane

Kristen
Test

22859 Posts

Posted - 2010-03-18 : 13:32:30

quote:
Originally posted by duanecwilson
I was only using the datatype that was in the table already

I guessed as much - that might mean that the datatype in the table is dubious! Are all the entries numeric? if so I would question why they are being stored as CHAR(n) - but you may have no control over that.

quote:

I was thinking about doing exact matches for the PAYROLL_CO_num and the PAYROLL_RC_num. Do you have to match datatypes and lengths in the parameters?

The thought I had was if you have a PAYROLL_CO_num value of "1234" and you want to search for "Starts with 1, or 2, and ends with 4, or 5" the RegEx for that would be "[12]__[45]" which is 10 characters long (even though the underlying field is only 4 characters) - so you might want to allow a "reasonably generous" parameter size to accommodate that. If none of users is going to do any searches which are quite so exotic! then you can tone-down my suggestion as you see fit

You also need to think about how trailing spaced will be treated. If someone searches for "1" because CHAR is fixed length that will become

"1   "

, and comparison may behave strangely. You'll have to experiment, we never use CHAR for anything other than single character, compulsory, fields so I'm not exactly sure how it behaves. You may be better off using VARCHAR for the parameter.

If your parameters will be 4 characters (never shorter) then using an equals test will be fine, and CHAR or VARCHAR will make no difference. Its only the shorter-than-4-characters scenario that you need to check (and if you might need longer RegEx expressions)

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-18 : 13:46:44

This is very informative. I am going to have to dedicate some time to wrapping my head around some of this "regex" stuff which I looked up online. It's pretty involved, it seems.

Duane

Kristen
Test

22859 Posts

Posted - 2010-03-18 : 14:21:57

Beware that SQL uses a cutdown version, so it lacks the fancy stuff that normal RegEx can do (Normal RegEx can be implemented in SQL, its just that "LIKE" only uses a subset)

"_" matches any single character

"%" matches zero, or more, characters

"[xxx]" is a closure. Matches exactly one character, from the "set". "[ABC0132456789]" matches A, B, C or a digit. "[A-C0-9]" is shorthand for the same thing.

Start a closure with "^" to not match the character(s) - "[^ABC]" or ""[^A-C]" matches a single character that is NOT A, B or C.

If you want to use a hyphen it has to come first - so as not to be considered a "range". "[-A-C0-9]" matches "-", A, B, C or a Digit.

You can escape the special characters either by putting them in a closure "[[]" matches "["

or by using an escape character - which is a bit painful! - LIKE '\]' ESCAPE '\'

That's about it!

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-18 : 15:59:13

Well thanks again. That's a good capsule summary and probably as much as I will ever need normally.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources