sql - 存储过程中的条件排序

Question

例如我有这个存储过程

Create Procedure sampleProcedure
AS
BEGIN 
Select EmpID, Name, Address, PhoneNumber From EmpTable
END

现在在我的应用程序中，我将如何执行此操作:例如，用户选中了 checkBox1，则上面的查询将为 ORDER BY EmpID，否则如果选中了 checkBox2，则查询将为 按名称排序。我必须创建两个程序吗？

Create Procedure sampleProcedure1
AS
BEGIN 
Select EmpID, Name, Address, PhoneNumber From EmpTable Order By EmpID
END
Create Procedure sampleProcedure2
AS
BEGIN 
Select EmpID, Name, Address, PhoneNumber From EmpTable Order By Name
END

如果复选框 1 被选中，那么 sampleProcedure1 将被执行，否则如果 checkbox2 被选中，那么 sampleProcedure2 将被执行？

Answer 1

为过程引入一个参数来指示排序顺序:

Create Procedure sampleProcedure1
  @orderByEmpId bit
AS
BEGIN 
  if (@orderByEmpId = 1)
    Select EmpID, Name, Address, PhoneNumber From EmpTable Order By EmpID
  else
    Select EmpID, Name, Address, PhoneNumber From EmpTable Order By Name
END

您可以进一步细化为:

CREATE PROCEDURE sampleProcedure1
  @orderByEmpId Bit
AS BEGIN 
  SELECT
    EmpID, Name, Address, PhoneNumber
  FROM EmpTable
  ORDER BY CASE WHEN @orderByEmpId = 1 THEN EmpID ELSE Name END
END