SQL Server - How to switch between 2 possible SELECT statements
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
开发者_如何学C ELSE SELECT UserName END
FROM Users
WHERE UserID < 5
Is there any way to achieve somehting like that?
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, GetAllUsers
and GetAllUsersWithDetails
.
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.
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.
精彩评论