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!
4条答案
按热度按时间jk9hmnmh1#
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
.ep6jt1vc2#
This should work for you:
t9aqgxwy3#
thanks you @MartinSmith for pointing this out, all I had to do was remove the THEN and END in my code above:
Thanks!
57hvy0tb4#
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.
While your table/columns is "small", i have seen horribly fragile code....like the below
It can get so out of control....quickly, IMHO.