In this article I will explain with an example, how to use LIKE operator (statement) in Stored Procedures with SQL Server database.
In Stored Procedures, the LIKE operator (statement) is used with the Stored Procedure parameters.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Stored Procedure using LIKE operator (statement)
The following Stored Procedure accepts a parameter i.e. @SearchTerm. The value of the parameter is used along with LIKE operator (statement) in a SELECT statement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchCustomers]
@SearchTerm NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID
,ContactName
FROM Customers
WHERE ContactName LIKE '%' + @SearchTerm + '%'
END
GO
In the above Stored Procedure, the LIKE operator (statement) works as CONTAINS where it looks for the match throughout the string value.
You can also use it as STARTS WITH and ENDS WITH options as shown below.
STARTS WITH
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchCustomers]
@SearchTerm NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID
,ContactName
FROM Customers
WHERE ContactName LIKE @SearchTerm + '%'
END
GO
ENDS WITH
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchCustomers]
@SearchTerm NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID
,ContactName
FROM Customers
WHERE ContactName LIKE '%' + @SearchTerm
END
GO
Screenshot
In the following screenshot the above Stored Procedure is executed and it is displaying the matching records found using LIKE operator (statement).
![SQL Server: Using LIKE operator (statement) in Stored Procedures]()
Downloads
