如何用多个条件语句编写存储过程？ [英] How to write stored procedure with multiple conditional statement?

问题描述

要求：



GetEmployees将有以下参数。
$ b $b@ EmployeeName - 默认为NULL

@ Country - 默认值为NULL
$ b $b@ Department - 默认为NULL
$ b $b@ SAastartStart - 默认为NULL
$ b $b@ SalaryEnd - By默认NULL



示例，


o Exec GetEmployees NULL，NULL，NULL，NULL，NULL - 这将列出所有员工。

o Exec GetEmployeesROCK，NULL，NULL，NULL，NULL - 这将列出按部门在EmployeeName顺序中包含单词ROCK的所有员工。

o Exec GetEmployeesROCK，NULL，NULL，500，NULL - 这将列出在EmployeeName中有ROC字样的所有员工



我有什么试过：



Alter proc GetEmployeedata（@EmployeeName varchar（50）= null，@ Country varchar（50）= null，

@Department varchar（50）= null，@ SalaryStart decimal（10,2）= null，@ SalaryEnd decimal（10,2）= n ull）

As

开始

选择e.empId，e.empName，e.Salary，e.Dateofbirth，c.country，d来自员工的.departmentName

加入部门d

on e.departmentId = d.departmentId

加入国家c

on e.countryId = c.countryId

其中（e.empName喜欢'％'+ @ EmployeeName +'％'或ISNULL（@EmployeeName，''）=''）和

（c.country喜欢'％'+ @ Country +'％'或ISNULL（@Country，''）=''）和

（d.departmentName喜欢'％'+ @部门+'％'或ISNULL（@Department，''）=''）和

（e.Salary> @SalaryStart或ISNULL（@SalaryStart，''）=''）和

（e.Salary< @SalaryEnd或ISNULL（@SalaryStart，''）=''

end

Requirement:

GetEmployees will have following parameters.
 @EmployeeName - By Default NULL
 @Country - By Default NULL
 @Department - By Default NULL
 @SalaryStart - By Default NULL
 @SalaryEnd - By Default NULL

examples,

o Exec GetEmployees NULL, NULL, NULL, NULL, NULL – This will list all employees.
o Exec GetEmployees "ROCK", NULL, NULL, NULL, NULL – This will list all employees who has word "ROCK" in EmployeeName order by department.
o Exec GetEmployees "ROCK", NULL, NULL, 500, NULL – This will list all employees who has word "ROC" in EmployeeName

What I have tried:

Alter proc GetEmployeedata (@EmployeeName varchar(50)=null,@Country varchar(50)=null,
@Department varchar(50)=null,@SalaryStart decimal(10,2)=null,@SalaryEnd decimal(10,2)=null)
As
Begin
select e.empId,e.empName,e.Salary,e.Dateofbirth,c.country,d.departmentName from Employee e
join Department d
on e.departmentId=d.departmentId
join Country c
on e.countryId=c.countryId
where (e.empName Like '%'+@EmployeeName+'%' or ISNULL(@EmployeeName,'')='') and
(c.country Like '%'+@Country+'%' or ISNULL(@Country,'')='') and
(d.departmentName like '%'+@Department+'%' or ISNULL(@Department,'')='') and
(e.Salary>@SalaryStart or ISNULL(@SalaryStart,'')='') and
(e.Salary<@SalaryEnd or ISNULL(@SalaryStart,'')=''
end

推荐答案

这是可能的&不需要动态SQL。

按以下顺序重新排序查询参数

This is possible & does not require dynamic SQL.
Re-order your query parameters as per below

select e.empId,e.empName,e.Salary,e.Dateofbirth,c.country,d.departmentName from Employee e
join Department d
on e.departmentId=d.departmentId
join Country c
on e.countryId=c.countryId
where 
(@EmployeeName IS NULL OR (e.empName Like '%'+@EmployeeName+'%'))
and 
(@Country IS NULL OR (c.country Like '%'+@Country+'%') 
and 
(@Department IS NULL OR (d.departmentName like '%'+@Department+'%') 
and 
(@SalaryStart IS NULL OR (e.Salary>@SalaryStart)) 
and 
(@SalaryEnd IS NULL OR (e.Salary<@SalaryEnd))

使用特定数据类型，您可能会发现这会返回意外结果。在这些情况下，我通常使用备用值，例如-1表示INT，空字符串表示varchar等。



亲切问候

With specific datatypes you may find this returns unexpected results. In these cases I typically use an alternate value, such as -1 for INT, empty string for varchar etc.

Kind Regards

这篇关于如何用多个条件语句编写存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！