In most cases LIKE conditions should by followed by ESCAPE clauses. Let me give you an example. Consider the following table, sample data, and stored procedure:
CREATE TABLE Data.Messages(Subject VARCHAR(30), Body VARCHAR(100));
GO
INSERT INTO Data.Messages(Subject, Body)
SELECT 'Next release delayed',
'Still fixing bugs' UNION
ALL
SELECT 'New printer arrived',
'By the kitchen area'
GO
CREATE PROCEDURE Readers.SelectMessagesBySubjectPattern
@SubjectPattern VARCHAR(100)
AS
SELECT Subject, Body
FROM Data.Messages
WHERE Subject
LIKE @SubjectPattern
GO
Apparently the procedure works, does it not:
EXEC Readers.SelectMessagesBySubjectPattern '%printer%'
GO
Let me add some more data:
INSERT INTO Data.Messages(Subject, Body)
SELECT '[OT] Great vacation in Norway!', 'Pictures already uploaded
to our Picasa album' UNION ALL
SELECT '[OT] Great new camera',
'Used it on my vacation, the pictures are great!'
GO
The following call returns nothing:
EXEC Readers.SelectMessagesBySubjectPattern '[OT] Great%'
GO
The reason is simple: because square brackets in a pattern are special characters, this pattern returns true only for subjects beginning with 'O Great' or 'T Great'. See for yourself:
INSERT INTO Data.Messages(Subject, Body)
SELECT 'O Great test message',
'test message'
EXEC Readers.SelectMessagesBySubjectPattern '[OT] Great%'
GO
O Great test message test message
You have a choice: you can either have a CHECK constraint disallow special characters, or you can fix the procedure. In this case, square brackets in the subject line are considered acceptable, so the procedure must change. To fix the procedure, simply add an ESCAPE clause, as follows:
GO
ALTER PROCEDURE Readers.SelectMessagesBySubjectPattern
@SubjectPattern VARCHAR(100)
AS
SELECT Subject, Body
FROM Data.Messages
WHERE Subject
LIKE @SubjectPattern ESCAPE
'\'
GO
You can invoke it and see for yourself that it works:
EXEC Readers.SelectMessagesBySubjectPattern '\[OT\] Great%'
[OT] Great vacation in Norway! Pictures already uploaded to our Picasa album
[OT] Great new camera Used it on my
vacation, the pictures are great!
Next post in this series:
Avoid
mixing old and new styles of error handling.