How to execute dynamic query in sql server stored procedure?
Introduction
Dynamic Sql query in store procedure allows to do multiple searches using Where clause. It allows you to optional parameters in the WHERE clause. It is basically used for reports or screens that have multiple, optional search criteria. This article will tell you the simple way to create Stored Procedures and also execute it well and resist SQL injection attacks.
Example of Dynamic SQL Query
Create PROCEDURE [dbo].[proc_TCAStatus]
@Type INT,
@TrainingDateType INT =1,
@SDate datetime ='',
@EDate datetime ='' ,
@VoucherNo varchar(90)='',
@Location Varchar(90)='',
@Country varchar(90)='' ,
@CourseName varchar(500)=''
AS
BEGIN
select sg.StudnetCardId,sg.SCFor,sg.SCStatus,sg.TrainingSDate,sg.TrainingEDate,sg.CorporateId,sg.CustomerType,sg.NoOfParticipants,enq.Country
,enq.Owner,enq.Name as TrainingCordinatorName,STUDENTNAME,CourseName,SelectedExam as ExamNo
from TRAN_SCGENERALINFO sg inner Join TRAN_ENQUIRY enq on enq.LeadId=sg.LeadId
join TRAN_SCINTERESTEDCOURSES ic on ic.StudnetCardId=sg.StudnetCardId
join MST_STUDENTINFO std on std.STUDENTCARDID=sg.StudnetCardId
where
(sg.TrainingSDate between @SDate and @EDate)
and (ic.CourseName=@CourseName or @CourseName='') and (ic.LocationId=@Location or @Location='')
order by sg.TrainingSDate desc
option(recompile)
END
IF @TrainingDateType = 2
BEGIN
select sg.StudnetCardId,sg.SCFor,sg.SCStatus,sg.TrainingSDate,sg.TrainingEDate,sg.CorporateId,sg.CustomerType,sg.NoOfParticipants,enq.Country
,enq.Owner,enq.Name as TrainingCordinatorName,STUDENTNAME,CourseName,SelectedExam as ExamNo
from TRAN_SCGENERALINFO sg inner join TRAN_ENQUIRY enq on enq.LeadId=sg.LeadId
join TRAN_SCINTERESTEDCOURSES ic on ic.StudnetCardId=sg.StudnetCardId
join MST_STUDENTINFO std on std.STUDENTCARDID=sg.StudnetCardId
where
(sg.TrainingEDate between @SDate and @EDate)
and (ic.CourseName=@CourseName or @CourseName='') and (ic.LocationId=@Location or @Location='')
order by sg.TrainingSDate desc
option(recompile)
END
END
Execute Store Procedure
Exec [proc_TCAStatus] @SDate='01-Feb-2016',@EDate='15-Feb-2016'
