Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained that how to use LIKE operator or statement in Stored Procedures with SQL Server. In Stored Procedures, the LIKE operator or statement is used with passing the parameters to Stored Procedure. This article is applicable for all versions of SQL Server i.e. 2000, 2005, 2008, 2012, 2014, 2019, 2022 and higher.
Join our Channel on Whtsapp and Telagram for All Updates
Database
In this article we used of Microsoft’s Northwind Database. You can download Microsoft’s Northwind Database from Microsoft’s Website.
Stored Procedure by using the LIKE operator or statement in SQL Server
The used Stored Procedure accepts a parameter named @Search. The passing value of the parameter is used with LIKE operator or statement) in a SELECT statement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Search_Customers]
@Search NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID
,ContactName
FROM Customers
WHERE ContactName LIKE '%' + @Search + '%'
END
GO
In the above created Stored Procedure, the LIKE operator or statement will be work as bottle up or hold where it will compare or match the whole value of all the string.
We can also write it STARTS WITH and ENDS WITH options as showing below.
STARTS WITH
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Search_Customers]
@Search NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID
,ContactName
FROM Customers
WHERE ContactName LIKE @Search + '%'
END
GO
ENDS WITH
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Search_Customers]
@Search NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID
,ContactName
FROM Customers
WHERE ContactName LIKE '%' + @Search
END
GO
Output
In the below image you can check the output of the above created Stored Procedure after execution and this will be showing the matched found records as per the input by using LIKE operator or statement.
Conclusion: In above code, I have been explained that how to use LIKE operator or statement in Stored Procedures with SQL Server. This code is very helpful for every developer. Bye and take care of your Developers. We will come back shortly with the new article.
Regards
Programming Hub
0 comments:
Post a Comment