1. Overview
This document talks about how to use the search operation for all the reports using MSSQL server Stored Procedure.
[Note: Steps included in this document will only works when you are using MSSQL Server]
2. Technologies and Tools Used
The following technologies have been used for common search using stored procedure.
- MSSQL Server 2012,2014 … 2022
3. Use Case
Assume that there is a requirement to search a string in common search text box for all the report using MSSQL server stored procedure.
4. Architecture
Following steps explains in detail,
- Before Creating a stored procedure in MSSQL server 2012 (we can use latest version also) we need to create a sample tables in MSSQL server as below
| Table Name | Comments |
| TSTMP01User | This is the main parent table for USER table |
| TSTMC01User | This is child table for User Table |
create-insert-exec-query-for-common-search-functionality
Note: Create, insert, exec query script has been attached in the above text file. And this table has been created in tempdb (This is sample DB and you can create any database as your wish).
Step 2: Once required table creation and records insertion was over we need to create a common search stored procedure as below
Name: sp_common_search
Scheme Name: tempDB (this is sample DB and you can create this stored procedure at any database)
MSSQL Stored Procedure Description:
USE [tempdb]
GO
/****** Object: StoredProcedure [dbo].[sp_common_search] Script Date: 12-07-2023 17:23:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_common_search]
(
@ScreenName varchar(100)=Null,
@Page varchar(10)=Null,
@RecordsPerPage varchar(10)=’10’,
@SearchBy varchar(100)=Null,
@SearchValue varchar(100)=Null,
@RecordCount varchar(10) output
)
as
/* Author : K.Raman Date : 12-07-2023 Functionality : Common Search For All Screen */
begin
Declare @Count table(RecordCount bigint)
— declare @text varchar(max)
if(@ScreenName=’Frm_M_User’)
begin
exec(‘select RowNumber, MP01Key,UserName,PassWord,MP02Role,MP01Status,UserFullName,fatherName,MotherName,UserAddress from(
select ROW_NUMBER() over (order by MP01Key) as RowNumber, MP01Key,UserName,PassWord,MP02Role,case MP01Status when 0 then ”Inactive” else ”Active” end as MP01Status,
UserFullName,fatherName,MotherName,UserAddress,case when MC01Status=0 then ”Inactive” else ”Active” end as MC01Status
from [dbo].[TSTMP01User] a
inner join TSTMC01User b on a.MP01KEY=b.MP01UserKey
where (‘+@SearchBy+’ like ”’+@SearchValue+’%”’+’)
)Temp
where RowNumber BETWEEN(‘+@Page+’ -1) * ‘+@RecordsPerPage+’ + 1 AND
(((‘+@Page+’ -1) * ‘+@RecordsPerPage+’ + 1) + ‘+@RecordsPerPage+’) – 1 ‘)
— print @text
Insert into @Count Exec (‘
select count(1) from TSTMP01User a
inner join TSTMC01User b on a.MP01KEY=b.MP01UserKey
where (‘+@SearchBy+’ like ”’+@SearchValue+’%”’+’) ‘)
Set @RecordCount=(Select RecordCount from @Count)
end
end
Successful message : “Command Completed Successfully”
Step 3 : Once stored procedure creation done need to run below query to find the search values for the respective report
/* Calling Stored Procedure */
Declare @RecordCount bigint ;
exec sp_common_search
@ScreenName=’Frm_M_User’,
@Page =’1′,
@SearchBy =’UserName’,
@SearchValue =’ra’,
@RecordCount = @RecordCount output
select @RecordCount as totalrecordcount;
Result 1:
/* Calling Stored Procedure */
Declare @RecordCount bigint ;
exec sp_common_search
@ScreenName=’Frm_M_User’,
@Page =’1′,
@SearchBy =’UserName’,
@SearchValue =’doyen’,
@RecordCount = @RecordCount output
select @RecordCount as totalrecordcount;
Result 2:
/* Calling Stored Procedure */
Declare @RecordCount bigint ;
exec sp_common_search
@ScreenName=’Frm_M_User’,
@Page =’1′,
@SearchBy =’UserAddress’,
@SearchValue =’%i%’,
@RecordCount = @RecordCount output
select @RecordCount as totalrecordcount;
Result 3:
5. Conclusion
we can use this single stored procedure for all the report to search any data and listing any report. This common search stored procedure we can use multiple project also. For this one we used only for MSSQL server.
