Using LIKE clause in Stored Procedures

I was working on a stored procedure today and had a complete brain fart as it relates to using the like clause in a query. Let me clarify a little more. I know in a simple query when trying to use a LIKE clause you most likely will use the wild card characters like this;

SELECT
Timestamp
, Message
, CategoryName
, MachineName
, ProcessName
FROM dbo.LogDB
WHERE ProcessName LIKE ‘%ProcessName%’

But I could not, for the life of me remember about using this in a stored procedure.
Like so;

ALTER PROCEDURE dbo.getLogs
@ProcessName NVARCHAR(512)

AS

BEGIN

SELECT
l.Timestamp AS ‘Timestamp’
, l.[Message] AS ‘Message’
, c.[CategoryName] AS ‘Category’
, l.[MachineName] AS ‘Machine’
, l.[ProcessName] AS ‘Process Name’
FROM [dbo].[Log] l
INNER JOIN [dbo].[CategoryLog] cl ON cl.LogID = l.LogID
INNER JOIN [dbo].[Category] c ON c.CategoryID = cl.CategoryID
WHERE l.ProcessName LIKE ‘%@ProcessName%’

Luckily, I copies of a lot of code I have done over the years and it only took a few minutes to look through my “bag of tricks” and find the solution. Here is the correct way.

ALTER PROCEDURE dbo.getLogs
@ProcessName NVARCHAR(512)

AS

BEGIN

SELECT
l.Timestamp AS ‘Timestamp’
, l.[Message] AS ‘Message’
, c.[CategoryName] AS ‘Category’
, l.[MachineName] AS ‘Machine’
, l.[ProcessName] AS ‘Process Name’
FROM [dbo].[Log] l
INNER JOIN [dbo].[CategoryLog] cl ON cl.LogID = l.LogID
INNER JOIN [dbo].[Category] c ON c.CategoryID = cl.CategoryID
WHERE l.ProcessName LIKE ‘%’ + @ProcessName + ‘%’

Notice the difference? When working within a stored procedure you must add in the additional code of;
‘%’ + @ProcessName + ‘% after the LIKE clause. Not too complicated.

Hope this helps.

Cheers!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s