I have a Store procedure with 8 optional parametes, if the optinal parameter passes when calling the store procedure it'll add the condition to the Query according to the parameter value.
This is My Store Procedure
ALTER PROCEDURE [dbo].[Copy_Search_Candidate_forcampus]
(
@Course_id bigint,
@Year_id bigint,
@Semester_id bigint,
@Criteria1 varchar(50)=null,
@Marks1 decimal(18,2)=null,
@Criteria2 varchar(50)=null,
@Marks2 decimal(18,2)=null,
@Criteria3 varchar(50)=null,
@Marks3 decimal(18,2)=null,
@Criteria4 varchar(50)=null,
@Marks4 decimal(18,2)=null
)
AS
BEGIN
set nocount on;
if 1=0
begin
set fmtonly off
end
BEGIN TRANSACTION
select a.*,
c.over_all_per MRes,
d.over_all_per HRes,
e.over_all_per DRes
from
admission_table1 a
join
Branch_Master b on a.Branch_Id=b.Branch_Id
left outer join
admission_table2 c on a.student_id=c.student_id and c.exam='10th'
left outer join
admission_table2 d on a.student_id=d.student_id and d.exam='12th'
left outer join
admission_table2 e on a.student_id=e.student_id and e.exam='Diploma'
where
a.Course_Id=@Course_id and
a.Semester_Id=@Semester_id and
a.Year_Id=@Year_id
and c.over_all_per>=80
COMMIT
IF @@ERROR<>0
begin
ROLLBACK
end
END
I want It Like This
select a.*,
c.over_all_per MRes,
d.over_all_per HRes,
e.over_all_per DRes
from
admission_table1 a
join
Branch_Master b on a.Branch_Id=b.Branch_Id
left outer join
admission_table2 c on a.student_id=c.student_id and c.exam='10th'
left outer join
admission_table2 d on a.student_id=d.student_id and d.exam='12th'
left outer join
admission_table2 e on a.student_id=e.student_id and e.exam='Diploma'
where
a.Course_Id=@Course_id and
a.Semester_Id=@Semester_id and
a.Year_Id=@Year_id
+++ADD HERE
if Criteria1 <>null +++ and c.over_all_per>@Marks1
if Criteria2 <>null +++ and d.over_all_per>@Marks2
if Criteria3 <>null +++ and e.over_all_per>@Marks3
If you don't understand the Question then Ask me in comments, i'm on.
actually i'm not a Good Describer.
Another thing, I know the Lengthy process to Solve it, by Checking all the parameter value and writing the query.
[edit]
i think you all didn't get my question clearly.
here Criteria1,2,3,4 is just the optional parameter and c,d,e.over_all_per is the same column from a single table.
and my condition is..
if Criteria2 <>null then d.over_all_per>@mark2 condition should apply +
if Criteria3 <>null then e.over_all_per>@mark3 condition should apply+
if Criteria4 <>null then f.over_all_per>@mark4 condition should apply
Student_table withh student_id PK Column
and Student_Exam with student_id FK column,exam,Mearks
here we checking on Student_Exam table on Student_id FK fro exam[Criteria1,2,3,4] and Marks[marks1,2,3,4]