question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked EchoLiu-msft commented

SQL Server how this where filters are working

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 commented

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;
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How sql server understand to return all when Ticker='' when using this filter

SELECT * FROM @T WHERE @Ticker = '' OR Ticker = @Ticker;

this is not clear to me that how sql server understand to return all rows when ticker variable is empty like @Ticker='' ?

please discuss & guide me in details. thanks

0 Votes 0 ·

If the parameter @Ticker is the empty string, the condition @Ticker = '' in the WHERE clause is the same as 1 = 1. So the SQL Server treats no filter on the Ticker.

0 Votes 0 ·

SELECT * FROM @T WHERE Ticker = ''; if mention this way then desired result will not come. or if i write this way

SET @Ticker=''
SELECT * FROM @T WHERE Ticker = @Ticker;

then also desired result will not come.

SELECT * FROM @T WHERE (@Ticker = '' OR Ticker = @Ticker)
so my question is how in this case sql server understand to return all data ?

0 Votes 0 ·
Show more comments
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How sql server understand to return all when Ticker='' when using this filter

SELECT * FROM @T WHERE @Ticker = '' OR Ticker = @Ticker;

this is not clear to me that how sql server understand to return all rows when ticker variable is empty like @Ticker='' ?

please discuss & guide me in details. thanks

0 Votes 0 ·

@Ticker is a variable, not a field in the table.

IF @Ticker = '' THEN

WHERE (@Ticker = '' OR Ticker = @Ticker)

equals

WHERE (TRUE OR FALSE) = TRUE for all records

1 Vote 1 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sir,
see this line WHERE (@Ticker ='' OR X.Ticker = @Ticker)

i understand this one X.Ticker = @Ticker but what is the meaning of @Ticker ='' ?

How sql server understand that if @Ticker variable is empty then return all data else return only selected ticker what is stored in this variable @Ticker variable ?

this is not clear. please help me to under.


thanks


0 Votes 0 ·

but what is the meaning of @Ticker =''

This is a convention between the caller and the stored procedure. If the caller passes @Ticker = 'Nisse', this instructs the stored procedure to look for rows where the Ticker column is = 'Nisse'. But if the caller passes @Ticker = '', this means that the stored procedure should not bother about checking the the Ticker column, but any value is good.

More commonly, such conditions are written as

Ticker = @Ticker OR @Ticker IS NULL

that is, the caller passes NULL to mean "anything goes". But that is just a convention that the caller and the stored procedure have agreed on.

1 Vote 1 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ErlandSommarskog edited

Hi @TZacks-2728,

  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".



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sir,
see this line WHERE (@Ticker ='' OR X.Ticker = @Ticker)

i understand this one X.Ticker = @Ticker but what is the meaning of @Ticker ='' ?

How sql server understand that if @Ticker is empty then return all ticker else return only selected ticker what is stored in this variable @Ticker ?

this is not clear. please help me to under. thanks


0 Votes 0 ·

WHERE (@Ticker ='' OR X.Ticker = @Ticker)

The function of this statement is to return rows where @Ticker is a null value or rows where X.Ticker = @Ticker.

In other words, the rows where @Ticker is a null value and the rows where X.Ticker = @Ticker will be returned.

What is stored in @Ticker depends on what value you assign to the variable.

Regards
Echo

0 Votes 0 ·

The function of this statement is to return rows where @Ticker is a null value or rows where X.Ticker = @Ticker.

No. If @Ticker is NULL, the query will return no rows at all. NULL and the empty string are two different things. NULL is an unknown value. The empty string is a known value.

As I said, the normal pattern is to use NULL for this kind of thing, but as I said in my reply to TZacks above, it is a convention between client and stored procedure what to use. In my opinion, NULL is a better choice, since NULL works with all data types.

0 Votes 0 ·