SQL: CASE or IF based on parameter with 2 different SELECTS

Having trouble searching or figuring this out, but imagine it should be pretty easy.

I have an employee table

NAME          ADMIN
-------------------------
Jim           Y
John          Y
Zack          N
Tim           N

I have a @parameter value which provides me the name that is running the report

I basically just want to return 2 different results based on if the parameter (user) is an admin

IF @NAME IN (SELECT NAME FROM employee where ADMIN = 'Y') THEN
       SELECT NAME FROM employee                        -- return all names in table
ELSE
       SELECT NAME FROM employee where NAME = @NAME     -- just return the 1 username
END

Can someone point my in the right direction on how to return this properly? thanks in advance!

You should resist any temptation to combine the two cases into a single SELECT statement as the best execution plans for each won't be the same (index seek of a single row vs full scan).

You are probably best off doing something like this so in the non admin case you only need to hit the row where Name = @Name once (rather than first to find out they are not an admin and again to get the actual values).

If they are not an admin you already have what you need assigned to scalar variables. If they are an admin you then move onto the full select.

DECLARE @Admin           CHAR(1),
        @SomeOtherColumn VARCHAR(50);

SELECT @Admin = Admin,
       @SomeOtherColumn = SomeOtherColumn
FROM   employee
WHERE  Name = @Name /*Needs to be unique key*/

IF @Admin = 'Y'
  SELECT Name,
         SomeOtherColumn
  FROM   employee
ELSE
  SELECT @Name AS Name,
         @SomeOtherColumn AS SomeOtherColumn
  WHERE @Admin IS NOT NULL /*If select to assign variables found no row return zero rows*/   

This should work for you:

SELECT NAME FROM employee where ADMIN = 'Y'
	IF(@@ROWCOUNT > 0) 
		SELECT NAME FROM employee                        -- return all names in table
	ELSE
		SELECT NAME FROM employee where NAME = @NAME     -- just return the 1 username

thanks you @MartinSmith for pointing this out, all I had to do was remove the THEN and END in my code above:

IF @NAME IN (SELECT NAME FROM employee where ADMIN = 'Y') 
       SELECT NAME FROM employee                  -- return all names in table
ELSE
       SELECT NAME FROM employee where NAME = @NAME  -- just return the 1 username

Thanks!

You have a little tension point.

Do you want "fragile code" where you have 2 paths for SELECT statements that can get out of sync?

Or do you want to keep fragility to maximize the query plan?

See my answer below. If your table is small, I prefer "non fragile" code.
Note, the version below has only ONE SELECT clause... but with a deviation on the where clause.

CREATE TABLE #MyTableOne (Name VARCHAR(16), IsAdmin CHAR(1) NOT NULL) 


INSERT INTO #MyTableOne (Name, IsAdmin)

-------------------------
SELECT 'Jim', 'Y'
UNION ALL SELECT 'John', 'Y'
UNION ALL SELECT  'Zack', 'N'
UNION ALL SELECT  'Tim', 'N'


DECLARE @NameParameter VARCHAR(128) 
/* experiment here with the different values */
SELECT @NameParameter = 'Jim'
SELECT @NameParameter = 'Tim'
SELECT @NameParameter = 'NotInTheTable'


DECLARE @MatchCount INT = 0

SELECT @MatchCount = COUNT(*) FROM #MyTableOne myAlias WHERE myAlias.Name = @NameParameter AND myAlias.IsAdmin = 'Y'


SELECT Name, IsAdmin FROM #MyTableOne myOuterAlias
WHERE
@MatchCount = 0 OR (myOuterAlias.Name = @NameParameter AND  myOuterAlias.IsAdmin = 'Y')


DROP TABLE #MyTableOne

While your table/columns is "small", i have seen horribly fragile code....like the below

IF @MagicValue = 1
BEGIN
     SELECT A, B, C from dbo.MyTable JOIN (some permutation of joins here version 1) where ( nasty where clause #1  )
END

IF @MagicValue = 2
BEGIN
     SELECT A, B, C from dbo.MyTable JOIN (some permutation of joins here version 2) where ( nasty where clause #2  )
END

IF @MagicValue = 3
BEGIN
     SELECT A, B, C from dbo.MyTable JOIN (some permutation of joins here version 3) where ( nasty where clause #3  )
END

It can get so out of control....quickly, IMHO.