SQL Server - How to switch between 2 possible SELECT statements

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.