Filter rows on basis of parameter provided by Stored Procedure

I am using a SQL Server database and have a table named MYTABLE which has data as shown below. I am trying to fetch the data from my stored procedure
that accepts @AllowedUser as parameter. The stored procedure is currently just simple select query

SELECT * FROM MYTABLE

However it returns all three rows. I wanted to filter out if the returned data has any encrypted row (Column : Encryption = 1), only then filter out this row on the basis of AllowedUser. So for example @AllowedUser parameter was 3, then I will get all three rows as shown

If @AllowedUser was 2, then I will get first and second row as shown below (It will be same for @AllowedUser = 1, @AllowedUser = 5 etc)

I had tried to to this by following way but I think it is not a good approach as I have to fetch the data twice from database

Step 1 :

SELECT Count(1)
FROM MYTABLE
WHERE Encryption = 1 -- (I fetched this result in a parameter)

If that parameter’s value is greater than 1 (Means there was an encrypted row), then I will filter out the result according to @AllowedUser

Else

I will simply execute the select statement.

Is there any better way to perform the above task without having to call the database twice? Can I use the CASE statement?

>Solution :

As best I can understand your requirements I think you just need a simple OR condition – but you still need to query the table twice to determine whether a user has an encryption row.

However since you are concerned about performance I have show a way to do it which* appears to only need to access the table a single time.

DECLARE @MYTABLE TABLE (PersonId int, [Encryption] bit, AllowedUser int);

-- 36% of execution plan
INSERT INTO @MYTABLE (PersonId, [Encryption], AllowedUser)
VALUES (123,0,1),(123,0,2),(123,1,3);

DECLARE @AllowedUser int = 3;

-- 12% of execution plan
SELECT *
FROM @MYTABLE mt
WHERE(
    -- Anyone can see unencrypted rows
    [Encryption] = 0
    -- Or if the user has a row with "[Encryption] = 1" then they can see all rows
    OR EXISTS (SELECT 1 FROM @MYTABLE mt1 WHERE [Encryption] = 1 AND AllowedUser = @AllowedUser)
);

-- 52% of execution plan
WITH cte AS (
    SELECT PersonId, [Encryption], AllowedUser
        , MAX(CASE WHEN AllowedUser = @AllowedUser THEN CONVERT(int, [Encryption]) ELSE 0 END) OVER (PARTITION BY AllowedUser) HasEncryption
    FROM @MYTABLE
)
SELECT *
FROM cte
WHERE [Encryption] = 0 OR HasEncryption = 1;

BUT this is not how SQL performance works. SQL is a descriptive language, you are describing the results you want. You are not telling the engine how to obtain those results. It works that out is a very complex manner taking into account indexes, statistics and other magic.

So in this simple case, when I turn on display execution plan, the CTE solution is running much slower than the double table query.

The real lesson here is, don’t try and pre-optimise your query, nor assume you know how a given query is going to perform (there are some basic rules of thumb of course). The general rule is, write the query in the most clear, logical manner you can, and if there are then performance issues, performance tune those queries.