Here we learn how to create dynamic query(red query) to fetch the details.
I have a table named QAMaster(Question Answer Master) with below columns (ID,QAType,Question,Answer,CreatedBy,IsActive,CreatedDate,ModifiedBy,ModifiedDate,IPAddress)
We write a dynamic query to fetch the details on condition of QAType,Question,CreatedDate,IsActive
Let's start :
Step 1: First write the basic syntax of creating procedure
Create Proc Proc_DynamicQuery
(
@QAType Varchar(50),
@Question Nvarchar(50),
@StartDate Varchar(12),
@EndDate Varchar(12),
@IsActive Bit
)
As
Begin
-- dynamic query
End
Step 2 : Now Declare two variable @DynamicQuery Nvarchar(Max)='',@DynamicCondition Nvarchar(Max)='' and set both blank value bydefault
Create Proc Proc_DynamicQuery
(
@QAType Varchar(50),
@Question Nvarchar(50),
@StartDate Varchar(12),
@EndDate Varchar(12),
@IsActive Bit
)
As
Begin
Declare @DynamicQuery Nvarchar(Max)='',@DynamicCondition NVarchar(Max)=''
-- dynamic query
End
Step 3 : Now we Write query select all record by default when user not give any condition then it will fetch all records.
and Set query to variable @DynamicQuery , as given below
Create Proc Proc_DyanmicQuery
(
@QAType Varchar(50),
@Question Nvarchar(50),
@StartDate Varchar(12),
@EndDate Varchar(12),
@IsActive Bit
)
As
Begin
Declare @DynamicQuery Nvarchar(Max)='',@DynamicCondition NVarchar(Max)=''
Set @DynamicQuery=N'Select ID ,QAType,Question,Answer,CreatedBy,IsActive
,CreatedDate,ModifiedBy,ModifiedDate,IPAddress
from QAMaster where 1=1'
End
Step 4: Now set the dynamic conditions to get the records based on the conditions.
as given below .
Alter Proc Proc_DyanmicQuery
(
@QAType Varchar(50),
@Question Nvarchar(50),
@StartDate Varchar(12),
@EndDate Varchar(12),
@IsActive Bit
)
As
Begin
Declare @DynamicQuery Nvarchar(Max)='',@DynamicCondition NVarchar(Max)=''
--------------------------Conditions--------------------------
IF @QAType<>'' Set @DynamicCondition+=' And QAType='''+@QAType+''''
IF @Question<>'' Set @DynamicCondition+=' AND Question LIKE ''%' + @Question + '%'''
IF IsDate(@StartDate)=1 and IsDate(@EndDate)=1 Set
@DynamicCondition+=' And CreatedDate Between '''+@StartDate+''' And '''+@EndDate+' 23:59:59.998'''
IF @IsActive is not null and @IsActive<>2 Set @DynamicCondition+=' And IsActive='''+Cast(@IsActive as Varchar)+''''
---------------------------Conditions--------------------------
Set @DynamicQuery=N'Select ID ,QAType,Question,Answer,CreatedBy,IsActive
,CreatedDate,ModifiedBy,ModifiedDate,IPAddress
from QAMaster where 1=1'+@DynamicCondition
--print(@DynamicQuery) this is for print the generated dynamic query
Execute sys.Sp_executesql
@stmt=@DynamicQuery,
@Param=N'
@QAType Varchar(50),
@Question Varchar(50),
@StartDate Varchar(12),
@EndDate Varchar(12),
@IsActive Bit',
@QAType=@QAType,
@Question=@Question,
@StartDate=@StartDate,
@EndDate=@EndDate,
@IsActive=@IsActive
End
As above query is ready to fetch the records on the based of dynamic conditions.
Let's check it .
- Fetch all records
Proc_DyanmicQuery '','','','',2
- Now Get the records on the base of Type
Proc_DyanmicQuery 'C#','','','',2
- Now get the records based on the question
Proc_DyanmicQuery 'C#','What is C#?','','',2
- Now get the records based on the CreatedDate
Proc_DyanmicQuery 'C#','','16-apr-2024','20-apr-2024',1
We hope you have understood the concept of creating procedure with dynamic query