Addition Condition In Store Procedure Query.

See more:

SQL

stored-procedure

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] --15,12,8,'10th',80.00
(
@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]