CASE for Parameter conditions in SP WHERE

Question

I have a stored procedures with 5 parameters that all default to -1. When they are all -1, the stored procedure returns all rows. Otherwise, I am trying to write the select statement to restrict based on the values passed. I had used a UNION between SELECT statements with the conditions but it was returning everything so I need a way to restrict only the passed values and no retriction for that parameter if -1 is passed. I thought of doing a CASE inside the WHERE but not sure how I would do that. It might be something like the following.

IF (@ComputerType > -1 OR @DriveType > -1 OR @EncryptionState > -1 OR 
@ProtectorType <> '-1' OR @CipherStrength > -1)
BEGIN -- Not ALL
-- restrict to parameter values
   SELECT computerType, ComputerName, DriveType, DriveLetter,
      Encryption, ProtectType, Cipher
   FROM computers
   WHERE 
     -- case @computertype > -1 and @computertype <> `100 then    @computertype=computerType 
    -- case @DriveType > -1 and @drivetype <> 100 then @drivetype = drivetype
    -- case @EncryptionState > -1 and @EncryptionState <> 100 then Encryption=@EncryptionState
   -- etc for each parameter


END
ELSE -- All
BEGIN
  -- select statement with no WHERE
END

If I were to write it using dynamic SQL, I'd do something like this:

DECLARE @SQL NVARCHAR(MAX)
SET @SQL =N'SELECT computerType, ComputerName, DriveType, DriveLetter,
      Encryption, ProtectType, Cipher
   FROM computers
   WHERE 1=1 '
IF (@ComputerType > -1 and @ComputerType<> 100)
    SET @SQL = @SQL + N'
    AND @ComputerType = ComputerType '
IF (@DriveType > -1 and @DriveType<> 100)
    SET @SQL = @SQL + N'
    AND @DriveType = DriveType '
--     IF FOR EACH PARAMETER
EXEC (@SQL)

However, I'm trying to eliminate dynamic SQL due to the possibility of SQL Injection. Any help wpuld be appreciated.

You need to use the conditional `WHERE` clause and also use the Default value as the `NULL` instead of the `-1`. Here is the one solution http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server — Mahesh, Aug 26 '15 at 17:22

score 1 · Answer 1 · answered Aug 26 '15 at 17:26

You can accomplish that with AND and OR operatoirs:

(case @computertype > -1 and @computertype <> 100 AND    @computertype=computerType) OR
(case @DriveType > -1 and @drivetype <> 100 AND @drivetype = drivetype) OR
(case @EncryptionState > -1 and @EncryptionState <> 100 AND Encryption=@EncryptionState)
-- etc for each parameter

score 0 · Answer 2 · answered Aug 26 '15 at 17:31

0

You can do this with the following (not using case statements)

WHERE (@computertype = -1 or computerType = @computertype)
and ....

Obviously, adding another clause for each parameter.

answered Aug 26 '15 at 17:31

Kristen B

83
2
8

You have that backwards - your `or` and `and` should be switched. – D Stanley Aug 26 '15 at 17:35

CASE for Parameter conditions in SP WHERE

2 Answers2