Stored procedure for search functionality in SQL Server

In this SQL Server tutorial, we will learn How to create a stored procedure in SQL Server for search functionality. We will also discuss different examples of search functionality. The complete list of topics discussed in this article is listed below.

• Stored procedure for search filter in SQL Server
• Search text in stored procedure in SQL Server
• Find stored procedure in SQL Server by name in all databases
• How to search stored procedure in SQL Server
• How to find function used in stored procedure in SQL Server
• SQL Server stored procedure dynamic filter

So, before creating a stored procedure to implement some search, first, we should understand how we can search a stored procedure in SQL Server.

How to search stored procedure in SQL Server

While working in SQL Server, we may be stuck in a situation where we want to use a particular stored procedure. But, we can’t remember the exact name of that procedure.

Luckily, there are some methods available in SQL Server to search a stored procedure. By using these methods, we can search a stored procedure by using some part of the name. We can also use text, table, or column names used within the stored procedures to search the procedure.

In this section, we will cover multiple ways to search a stored procedure in SQL Server.

Find stored procedure in SQL Server by name

A SQL Server database contains multiple system tables and views that store information regarding procedures. So, the simplest way to search a stored procedure is by using a SELECT statement to query these system tables or views. And to search for some part of the name of the procedure, we can use the LIKE operator.

Note:- The LIKE is a logical operator in SQL Server and it is used to checks whether a character string matches a particular pattern.

The detailed implementation to search a stored procedure using these tables and views is given below.

Using sys.procedures

The sys.procedures is a system table that stores information of each object which is a procedure in some way. Now, let’s understand how to use the SELECT statement and LIKE operator with this table. For this demonstration, we will search for a stored procedure with “Product” in the name.

USE sqlserverguides                     --selecting the database
GO

SELECT name FROM sys.procedures 
WHERE name LIKE '%Product%'
GO

And now, if we execute this procedure, it will return the names of all the procedures which contain “Product” in their name.

Using INFORMATION_SCHEMA.ROUTINES

The INFORMATION_SCHEMA.ROUTINES is a view in SQL Server. It contains information regarding each function and procedure for which the user has permission. It contains information like name, schema, definition, created, modified date, etc. Here is how we can use this view to find stored procedures.

USE sqlserverguides                     --selecting the database
GO

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE='PROCEDURE' AND
      ROUTINE_NAME LIKE '%Product%'

In the above example, we are using the INFORMATION_SCHEMA.ROUTINES view to find the stored procedure whose name contains “Product”. The above query will return the name and definition of the procedure which carries “Product” in their names.

Using sys.sql_modules

The sys.sql_modules is a system object catalog view in SQL Server. Now, we can also use this view to fetch a stored procedure. For demonstration, we have implemented the following example.

USE sqlserverguides
GO

SELECT OBJECT_NAME(object_id), definition
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND
      OBJECT_NAME(object_id) LIKE '%Product%'
GO

In the above example, we are using the sys.sql_modules to fetch the name and definition of the procedure which carries “Product” in their names.

Read SQL Server stored procedure insert into with examples

Find stored procedure in SQL Server by name in all databases

Till now, we are searching for the stored procedure within a known database. Now, what if we don’t know the database name that contains the required stored procedure which we are searching for.

In such cases, we have to search the stored procedures in all the databases available in SQL Server. And to search all the databases, we have to use the sp_msforeachdb procedure.

The sp_msforeachdb is an undocumented procedure available in SQL Server. And it is used to execute the same command for all the databases. The syntax to use the sp_msforeachdb is given below.

EXEC sp_msforeachdb command

As sp_msforeachdb is a procedure in SQL Server so, we need to execute it by using the EXEC statement. And to execute a command, we have to specify the command as a parameter to the procedure. Also, the command should be a variable-length string.

Next, let’s execute a query to search a procedure using sp_msforeachdb. Now, there can be two situations, first, when we know the exact name of the procedure. And second, when we don’t know the exact name of the stored procedure.

Let’s first execute the query for the situation where we already know the exact name of the stored procedure. For this, let’s consider the following example.

EXEC sp_msforeachdb 
'if exists(select 1 from [?].sys.procedures 
where name=''usp_GetProductById'')
select ''?'' as [Database], name from [?].sys.procedures where name=''usp_GetProductById'''

In the above example, we are trying to find the “usp_GetProductById” procedure. And we are using the SELECT statement to fetch the database and procedure name if the procedure is found. Now, if the procedure is found, we will get the database name in which the procedure exists.

Next, let’s execute the query for the situation where the exact name of the stored procedure is not known. For this, let’s consider the following example.

EXEC sp_msforeachdb 
'if exists(select 1 from [?].sys.procedures where name LIKE ''%get%'')
select ''?'' as [Database], name from [?].sys.procedures where name LIKE ''%get%'''

In this case, we are using the LIKE clause to find the procedure name which contains “get” in its name. And in our case, we will get the following result.

Read SQL Server stored procedure return value

Search text in stored procedure in SQL Server

So till this point in this article, we have learned how to search a stored in SQL Server. Next, let’s understand how to search for something within a stored procedure. For example, searching a variable name used in the body of the procedure.

Now, there are many ways to search for something within a stored procedure. In this section, we will discuss a standard way that can be used to search an item within the procedure.

Let’s understand this standard approach by executing an example. And for this, consider the given query.

SELECT Name, db_name() AS [Database name]  FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%ProductTable%'

In the above example, we are trying to find all the procedures in a database that contains “ProductTable” in the procedure definition. And if there is any procedure that contains “ProductTable”, the query will return the name of that procedure.

Note:- You can use any object name or text in place of “ProductTable” to find something.

Read: How to call a view in SQL Server

How to find function used in stored procedure in SQL Server

In this section, we will understand how we can find the function name used within the procedure. Now, there are many methods to find a function within a procedure. And in this section, we will cover the simplest way to fetch the name of the function.

For this implementation, we have to use the sp_depends procedure. The sp_depends is a system stored procedure that returns the information regarding dependencies of a database object. When we use a function within a procedure, that procedure’s execution now depends upon the function. So, if a procedure contains a function, the sp_depends will return the name of the function in the dependencies list.

Let’s understand this by executing a small example. And for this, we will try to use the sp_depends on the GetDeptRecords procedure.

USE sqlserverguides;  
GO  
EXEC sp_depends @objname = N'GetDeptRecords'; 
GO

It will return the list of all the dependencies of the procedure GetDeptRecords. In addition, it will also return the type of object on which the procedure depends. And from the type, we can easily determine the function and its name.

In our case, the procedure uses “dbo.udf_GetDeptRecords” function for selecting 2 columns.

Read SQL Server stored procedure parameters

Stored procedure for search filter in SQL Server

In this section, we will understand how we can create a stored procedure in SQL Server to search and filter out table data. In SQL Server, we generally use the WHERE clause to filter the results based upon some condition. And for searching, we can use the LIKE operator to find the result.

Let’s understand this execution, by implementing an example on a stored procedure that is used to search and filter out data based upon some condition. For example, demonstration, consider the following query.

CREATE PROC usp_GetrecordsByName
( @name VARCHAR(MAX) )
AS
BEGIN
  SELECT [first_name], [last_name], [gender], [country] 
  FROM [SampleTable]
  WHERE [first_name] LIKE @name+'%'
END

In the above example, we have created a procedure that will filter out the records from a table based upon the starting character of the name. It will be used to find the records from a table whose name starts with specific letters. In the query, we have used the WHERE clause, and in the clause, we have used the LIKE operator to find the starting character.

Next, let’s try to execute the procedure and try to search records whose name starts with “A”.

So, according to the output, there are a total of 3 records in the sample table whose name starts with “A”. And all these records are from different countries like the United States, Australia, and New Zealand.

SQL Server stored procedure dynamic filter

In this section, we will understand how to create a stored procedure in SQL Server that can be used for dynamic filtering of records. By dynamic filtering, we simply mean that a stored procedure will filter out results based upon the input provided.

To create a stored procedure for dynamic filtering, a procedure must accept some input parameters. And, it will use the parameters to find and filter the data to get the desired output. The simplest way to understand this approach is by executing an example. And for this, we will create a procedure that will filter the results based upon country name and joining date.

CREATE PROC usp_FilterRecods
( @country VARCHAR(MAX), @start_date DATE, @end_date DATE )
AS
BEGIN
  SELECT [first_name], [last_name], [country], [Date] AS [Joining Date]
  FROM [SampleTable]
  WHERE ([Country] = @country AND ([Date] BETWEEN @start_date AND @end_date))
END

In the example, we are using the WHERE clause to filter out results based upon the country name and joining date of an employee. And for the dates, we have used 2 date variables to define a range for the joining date.

Next, let’s execute the procedure by using the following inputs.

DECLARE @country VARCHAR(MAX) = 'United States',
        @start_date DATE = '2020-09-01',
	@end_date DATE = '2021-07-01'

EXEC usp_FilterRecods @country, @start_date, @end_date

And for the following inputs, we will get the following result.

So, from the output, we can observe that the query has returned all the records from “United States” with the specified joining date.

You may like the following SQL server tutorials:

• Try catch in SQL Server stored procedure with examples
• SQL Server stored procedure naming convention and best practices
• SQL Server stored procedure if else
• How to get list of users in SQL Server
• SQL Server stored procedure if exists update else insert
• SQL Server select from stored procedure (9 Examples)
• Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

So, in this tutorial, we have learned How to create a stored procedure in SQL Server for search functionality. We have also discussed different examples of search functionality, and we have covered the following topics.

• Stored procedure for search filter in SQL Server
• Search text in stored procedure in SQL Server
• Find stored procedure in SQL Server by name in all databases
• How to search stored procedure in SQL Server
• How to find function used in stored procedure in SQL Server
• SQL Server stored procedure dynamic filter