SQL Server - 如何在 2 个可能的 SELECT 语句之间切换
我想在我的 mssql 存储过程中使用一个参数来在小结果和详细结果之间切换（出于可维护性、性能和网络负载的原因）。
如果参数设置为 1，我将获取所有列，否则仅获取一两个最重要的列。 它的工作方式非常有限，如下所示：
ALTER PROCEDURE [dbo].[GetAllUsers]
@detail BIT
AS
IF @detail = 1 SELECT UserName, Title, UserID FROM Users
ELSE SELECT Username FROM Users
但我想使用组合的 WHEN 子句。 以下是我尝试过的，但这不起作用。
ALTER PROCEDURE [dbo].[GetAllUsers]
@detail BIT
AS
CASE @detail
WHEN 1 THEN SELECT UserName, Title, UserID
ELSE SELECT UserName END
FROM Users
WHERE UserID < 5
有什么办法可以实现这样的目标吗？
I would like to use a parameter in my mssql stored procedures to switch between a small and a detailed result (for maintainability, performance and network load reasons).
If parameter is set to 1 i get all columns, else only the one or two most important.
In a very limited way it works like this:
ALTER PROCEDURE [dbo].[GetAllUsers]
@detail BIT
AS
IF @detail = 1 SELECT UserName, Title, UserID FROM Users
ELSE SELECT Username FROM Users
But I want to use a combined WHEN clause.
The following is what i tried, but this doesnt work.
ALTER PROCEDURE [dbo].[GetAllUsers]
@detail BIT
AS
CASE @detail
WHEN 1 THEN SELECT UserName, Title, UserID
ELSE SELECT UserName END
FROM Users
WHERE UserID < 5
Is there any way to achieve somehting like that?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为让存储过程根据输入变量返回不同的记录类型是糟糕的 API 设计。我认为您应该创建两个存储过程，
GetAllUsers和GetAllUsersWithDetails。I think it is terrible API design to have a stored procedure return different record types based on an input variable. I think you should create two stored procedure,
GetAllUsersandGetAllUsersWithDetails.就我个人而言，我会使用两个不同的存储过程。没有理由仅仅为了强制事物适合一个存储过程而创建复杂的实现。
Personally, I would use two different stored procedures. There is no reason to create a complex implementation simply to force things to fit inside one stored procedure.
虽然我同意klausbyskov两个程序是更好的设计，无论如何我都会尝试回答你的问题 - 恐怕简短的答案是否。
确实可以根据变量的值执行两个不同的选择语句（这是您在第一个代码示例中所拥有的），但不可能更改投影< /em> 以这种方式在单个选择语句中。
While I agree witht klausbyskov that two procedures are a better design, I will try to answer your question anyway - and I am afraid that the short answer is no.
It is indeed possible to execute two different select statements (which is what you have in your first code sample) depending on the value of a variable, but it is not possible to alter the projection inside a single select statement this way.