SQL Server how this where filters are working

Question

see the code first

CREATE PROC USP_GetLastCSMSavedData      
(      
    @Ticker VARCHAR(10)='',      
    @ClientCode VARCHAR(10)='',      
    @LastCSMDate VARCHAR(10)='',      
    @PageIndex INT = 1,      
    @PageSize INT = 10      
)      
AS      
BEGIN      
 DECLARE @SQL VARCHAR(MAX)      
 DECLARE @ClientName VARCHAR(200)      

 IF @ClientCode<>''      
 BEGIN      
  SELECT @ClientName=ClientName FROM tblClient WHERE ClientCode=@ClientCode      
 END      
 ELSE      
 BEGIN      
  SET @ClientName=''      
 END      

    DECLARE @offset INT      
    SET @offset = (@PageIndex - 1) * @PageSize      

    SELECT * FROM (      
        SELECT CAST(ROW_NUMBER() OVER (ORDER BY LastCSMDeliveredDate DESC) AS INT) AS 'RowNumber',      
        ID,      
        Ticker,      
        c.ClientName,      
        Earnings,      
        PrePost,      
        IIF([QC-ViewAllContent] IS NULL,0,1) HasViewAllContent,      
        IIF([QCCommentsContent] IS NULL,0,1) HasQCCommentsContent,      
        InsertedOn,      
        LastCSMDeliveredDate,      
        IIF([Action]='I','INSERTED','UPDATED') AS [Action],      
        UserName      
        from tblLastCSMDelivered csm       
        JOIN tblClient c      
            ON csm.ClientCode=c.ClientCode      
        WHERE LastCSMDeliveredDate IS NOT NULL      
    ) X      
    WHERE CAST(X.RowNumber AS INT)> @offset  AND CAST(X.RowNumber AS INT)<= (@offset+@PageSize)      
    AND (@Ticker ='' OR X.Ticker = @Ticker)      
    AND (@ClientName ='' OR X.ClientName = @ClientName)      
    AND (@LastCSMDate ='' OR CAST(X.LastCSMDeliveredDate AS DATE)=CAST(@LastCSMDate AS DATE))      
 ORDER BY X.LastCSMDeliveredDate DESC    


END 

instead of writing dynamic sql i got a example from a site to apply filter like this way and it is working too but how it is working properly not clear to me.

WHERE 
        (@Ticker ='' OR X.Ticker = @Ticker)      
        AND (@ClientName ='' OR X.ClientName = @ClientName)      
        AND (@LastCSMDate ='' OR CAST(X.LastCSMDeliveredDate AS DATE)=CAST(@LastCSMDate AS DATE))      

@Ticker ='' OR X.Ticker = @Ticker again
@ClientName ='' OR X.ClientName = @ClientName

what is the meaning of above kind of filter? how these filters are working. please help me to understand. thanks

Answer 1

I think you do not want to filter the Ticker column if the parameter @Ticker is an empty string. See the following example:

DECLARE @Ticker VARCHAR(10) = '';
DECLARE @T TABLE (
    Ticker VARCHAR(10)
);

INSERT INTO @T VALUES
('T1'), ('T2'), ('');

-- Return all rows
SELECT * FROM @T WHERE @Ticker = '' OR Ticker = @Ticker;
-- Only return the rows with the empty string
SELECT * FROM @T WHERE Ticker = @Ticker;

Answer 2

If @Ticker ='' (blank) it returns all Ticker values.

Answer 3

The point of that WHERE clause is to permit searches on any combination of Ticker, ClientName or LastCSMDeliveredDate . Say for instance that

@Ticker = ''
@ClientName = 'Zacks'
@LastCSMDate = '2021-10-12'

The query will return rows where the ClientName is Zacks and the date is 12 Oct.

Answer 4

Hi @T.Zacks ,

 WHERE   
         (@Ticker ='' OR X.Ticker = @Ticker)        
         AND (@ClientName ='' OR X.ClientName = @ClientName)        
         AND (@LastCSMDate ='' OR CAST(X.LastCSMDeliveredDate AS DATE)=CAST(@LastCSMDate AS DATE))     

WHERE specifies the search condition for the rows returned by the query.

About operator precedence:
Use parentheses to override the defined precedence of the operators in an expression. Everything within parentheses is evaluated to yield a single value. That value can be used by any operator outside those parentheses.

If an expression has nested parentheses, the most deeply nested expression is evaluated first.

Operator Precedence (Transact-SQL)
1 ()
2 =
3 AND
4 OR
The number represents the priority, the smaller the number, the higher the priority. The priority determines the order of execution. Operators with higher priority are executed before operators with lower priority.

For details, please refer to:
Operator Precedence (Transact-SQL)

The above code returns all rows that meet the following three conditions:
1.@Ticker ='' OR X.Ticker = @Ticker
2.@ClientName ='' OR X.ClientName = @ClientName
3. @LastCSMDate ='' OR CAST(X.LastCSMDeliveredDate AS DATE)=CAST(@LastCSMDate AS DATE)

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".