存储过程查询中的添加条件。 [英] Addition Condition In Store Procedure Query.
问题描述
我有一个带有8个可选参数的存储过程，如果optinal参数在调用存储过程时通过，它将根据参数值将条件添加到Query中。
这是我的商店程序
ALTER PROCEDURE [dbo]。[Copy_Search_Candidate_forcampus] - 15,12,8，'10th'，80.00
（
@ Course_id bigint ，
@ Year_id bigint ，
< span class =code-sdkkeyword> @ Semester_id bigint ，
@ Criteria1 varchar （ 50 ）= null，
@ Marks1 十进制（ 18 ， 2 ）= null，
@ Criteria2 varchar （ 50 ）= null ，
@ Marks2 decimal （ 18 ， 2 ）= null，
@ Criteria3 varchar （ 50 ）= null，
@ Marks3 十进制（ 18 ， 2 ）= null，
@ Criteria4 varchar （ 50 ） = null，
@ Marks4 decimal （ 18 ， 2 ）= null
）
AS
BEGIN
set nocount 上;
如果 1 = 0
开始
set fmtonly off
end
BEGIN TRANSACTION
选择 a。*，
c.over_all_per MRes，
d.over_all_per HRes，
e.over_all_per DRes
来自
admission_table1 a
join
Branch_Master b a.Branch_Id = b.Branch_Id
left outer join
admission_table2 c on a.student_id = c.student_id 和 c.exam = ' 10th'
left outer join
admission_table2 d on a.student_id = d.student_id 和 d.exam = ' 12th'
left 外部 join
admission_table2 e on a.student_id = e.student_id 和 e.exam = ' 文凭'
其中
a.Course_Id=@Course_id 和
a.Semester_Id=@Semester_id 和
a.Year_Id=@Year_id
和 c.over_all_per> = 80
COMMIT
IF @@ ERROR<> 0
开始
ROLLBACK
end
< span class =code-keyword> END
我想要这样
选择 a。*，
c.over_all_per MRes，
d.over_all_per HRes，
e.over_all_per DR
来自
admission_table1 a
join
Branch_Master b on a.Branch_Id = b.Branch_Id
left 外部 加入
admission_table2 c a.student_id = c.student_id 和 c.exam = ' 第十'
left 外部 join
admission_table2 d a.student_id = d.student_id 和 d.exam = ' 12th'
left outer join
admission_table2 e on a .student_id = e.student_id 和 e.exam = ' 文凭'
其中
a.Course_Id=@Course_id 和
a.Semester_Id=@Semester_id 和
a.Year_Id=@Year_id
+++ ADD HERE
如果 Criteria1<> null +++ a nd c.over_all_per> @ Marks1
如果 Criteria2<> null +++ 和 d.over_all_per> @ Marks2
如果 Criteria3<> null +++ 和 e.over_all_per> @ Marks3
如果你不理解这个问题那么在评论中问我，我在。
实际上我不是一个好的描述者。
另一件事，通过检查所有参数值并编写查询，我知道解决它的冗长过程。
[edit]
i认为你们都没有清楚地得到我的问题。
这里Criteria1,2,3,4只是可选参数而c，d ，e.over_all_per与单个表中的列相同。
我的条件是..
如果Criteria2<> null那么d.over_all_per> @ mark2条件应该适用+
如果Criteria3<> null则e.over_all_per> @ mark3条件应适用+
如果Criteria4<> null则f.over_all_per> @ mark4条件应适用
Student_table withh student_id PK专栏
和Student_Exam with student_id FK专栏，考试，Mearks
这里我们在Student_id上查看Student_Exam表FK来自考试[Criteria1,2,3,4]和Marks [marks1,2,3,4]
你可以这样做：
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 AN D e.exam ='Diploma'
WHERE
a.Course_Id = @Course_id AND
a.Semester_Id = @Semeter_id AND
a.Year_Id = @Year_id AND
（Criteria = @ Criteria1 OR @ Criteria1 IS NULL）AND
（Marks = @ Marks1 OR @ Marks1 IS NULL）
尝试使用
其中
a.Course_Id=@Course_id 和
a.Semester_Id=@Semester_id 和
a.Year_Id=@Year_id 和
（Criteria1 null 或 c.over_all_per> @ Marks1 ）和
（Criteria2 是 null 或 d.over_all_per> @ Marks2 ）和
（Criteria3 null 或 d.over_all_per> @ Marks3 ）
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]
You could do something 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 AND (Criteria = @Criteria1 OR @Criteria1 IS NULL) AND (Marks = @Marks1 OR @Marks1 IS NULL)
try with
where a.Course_Id=@Course_id and a.Semester_Id=@Semester_id and a.Year_Id=@Year_id and (Criteria1 is null or c.over_all_per > @Marks1) and (Criteria2 is null or d.over_all_per > @Marks2) and (Criteria3 is null or d.over_all_per > @Marks3)
这篇关于存储过程查询中的添加条件。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
