Help deciphering Stored Procedure

Question

I can't for the life of me figure out how this stored procedure works or what it is doing exactly, I know it works as is but I'm trying to limit some database calls which requires modifying this stored procedure.

CREATE PROCEDURE scriptassist.getQueue
    @Status smallint = -1,
    @GroupID uniqueidentifier = null,
    @MACAddress varchar(200) = ''
AS
SET NOCOUNT ON

Print @GroupID

SELECT  *
FROM [Queue]
WHERE
    case @Status
        When -1 Then
            Case When ISNULL(Status,0) <> 1 Then 1 Else 0 End
        Else
            Case When ISNULL(Status,0) = @Status Then 1 Else 0 End
    End =1
And
    case When @GroupID IS NULL Then 1
        Else
            Case When GroupID = @GroupID Then 1 Else 0 End
    End =1
And
    case @MACAddress
        When ''
            Then 1
        Else
            Case When MACAddress = @MACAddress Then 1 Else 0 End
    End =1
Order By DateEntered DESC

I know that somehow it is dynamically defining the Where Clause but I'm not sure what is getting done. Also if anyone knows of a tool that would let me see what is actually happening, thanks!

Answer 1

Selects values from Queue based on three criteria that could be passed. If the passed variable @Status=-1, or @GroupID is null, or @MACAddress ='', they are not used in the Where criteria. If they are present, then they are used in the Where criteria.

When each case statement returns 1, the corresponding record in the Queue table is returned.

Answer 2

You're right in noticing that it's dynamically filtering the WHERE clause. Take for example

case @MACAddress
    When ''
        Then 1
    Else
        Case When MACAddress = @MACAddress Then 1 Else 0 End
End =1

If the MACAddress is the empty string then it is not used to filter the results. (The Case statement returs 1, which is then compared with 1 at the end, resulting in TRUE, so the row is included.)

If the @MACAddress parameter is not the empty string, then it's compared with the MACAddress column in the table. If there is a match 1, is returned, which is equal to 1, and so the row is included. If there is no match, 0 is returned, which does not equal 1 and the row is excluded.