none
Select Query Where 1=1.

    Question

  • Hi Gurus,

    I used select query followed by where clause 1=1 and followed by other filtering. The reason why I do so is because other filtering is based on the user input and its not mandatory. Is there any documentation or link which i can refer so i can prove that the method which I'm using is not confusing since i got feedback telling that 1=1 is creating unnecessary confusion. I was request to use if condition to check whether there is any valid filtering instead of 1=1.

    Any help is appreciated.

    Thanks.

    Thursday, September 08, 2011 3:32 AM

Answers

  • Thanks Gunasilan for you post and Kuldeeps' reply.

    where 1=1 is a old school way of avoiding sql injection if it is a dynamic query. Please feel free to choose the method which suits the best for you. The following is an example of dynamic query.

    CREATE PROCEDURE MyDynamicQuery       
    	@Param1 varchar(20) = NULL,         
    	@Param2 varchar(50) = NULL,         
    	@Param3 datetime = NULLAS 
     BEGIN   
    	DECLARE @sql nvarchar(1000)   
    	DECLARE @paramList nvarchar(1000)   
    	SELECT @paramList = N'@P1 varchar(20), @P2 varchar(50), @P3 datetime'   
    	SELECT @sql = N'SELECT Col1, Col2, Col3 FROM dbo.MyTable WHERE (1=1)'   
    	IF NOT (@Param1 IS NULL)      
    		SELECT @sql = @sql + N' AND (Col1 = @P1)'   
    	IF NOT (@Param2 IS NULL) 
    	BEGIN      
    		SELECT @Param2 = @Param2 + '%'      
    		SELECT @sql = @sql + N' AND (Col2 LIKE @P2)'   
    	END   IF NOT (@Param3 IS NULL)      
    		SELECT @sql = @sql + N' AND (Col3 >= @P3)'   
     
     --PRINT @sql   EXEC sp_executesql @sql, @paramList, @Param1, @Param2, @Param3   RETURNEND
    


    - Kerobin
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 4:56 AM
  • Hi

    I dont think its confusing But if you still need other solution then check following

     

     

    Declare @Param1 Varchar(50)
    Declare @Param2 Tinyint
    Select * From TableName Where Name=ISNULL(NULLIF(@Param1,''),Name) And Age=Isnull(Nullif(@Param2,0),Age)
    


     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Edited by Kuldeep Bisht Thursday, September 08, 2011 3:46 AM
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 3:45 AM
  • Confused by whom? by other developers?

    it is not confusion for true T-SQL Developers.

    if still confusion Kuldeep's reply is good to use.


    Thanks & Regards Prasad DVR
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 4:03 AM
  • If you construct your where clause dynamically, you can always do

    SET @Where = REPLACE(@Where,'1=1 AND','')

    In this case if nothing is selected you will have 1=1, but if something is selected, 1=1 will not be part of the where clause.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 4:46 AM
    Moderator
  • 1=1 means u have no conditions for the fetch. isnull(your_column,' ')=Isnull(@your_val,' ') can avoid some problem
    Many Thanks & Best Regards, HuaMin Chen
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:39 AM
    Thursday, September 08, 2011 6:27 AM

All replies

  • Hi

    I dont think its confusing But if you still need other solution then check following

     

     

    Declare @Param1 Varchar(50)
    Declare @Param2 Tinyint
    Select * From TableName Where Name=ISNULL(NULLIF(@Param1,''),Name) And Age=Isnull(Nullif(@Param2,0),Age)
    


     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Edited by Kuldeep Bisht Thursday, September 08, 2011 3:46 AM
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 3:45 AM
  • Confused by whom? by other developers?

    it is not confusion for true T-SQL Developers.

    if still confusion Kuldeep's reply is good to use.


    Thanks & Regards Prasad DVR
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 4:03 AM
  • If you construct your where clause dynamically, you can always do

    SET @Where = REPLACE(@Where,'1=1 AND','')

    In this case if nothing is selected you will have 1=1, but if something is selected, 1=1 will not be part of the where clause.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 4:46 AM
    Moderator
  • Thanks Gunasilan for you post and Kuldeeps' reply.

    where 1=1 is a old school way of avoiding sql injection if it is a dynamic query. Please feel free to choose the method which suits the best for you. The following is an example of dynamic query.

    CREATE PROCEDURE MyDynamicQuery       
    	@Param1 varchar(20) = NULL,         
    	@Param2 varchar(50) = NULL,         
    	@Param3 datetime = NULLAS 
     BEGIN   
    	DECLARE @sql nvarchar(1000)   
    	DECLARE @paramList nvarchar(1000)   
    	SELECT @paramList = N'@P1 varchar(20), @P2 varchar(50), @P3 datetime'   
    	SELECT @sql = N'SELECT Col1, Col2, Col3 FROM dbo.MyTable WHERE (1=1)'   
    	IF NOT (@Param1 IS NULL)      
    		SELECT @sql = @sql + N' AND (Col1 = @P1)'   
    	IF NOT (@Param2 IS NULL) 
    	BEGIN      
    		SELECT @Param2 = @Param2 + '%'      
    		SELECT @sql = @sql + N' AND (Col2 LIKE @P2)'   
    	END   IF NOT (@Param3 IS NULL)      
    		SELECT @sql = @sql + N' AND (Col3 >= @P3)'   
     
     --PRINT @sql   EXEC sp_executesql @sql, @paramList, @Param1, @Param2, @Param3   RETURNEND
    


    - Kerobin
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:38 AM
    Thursday, September 08, 2011 4:56 AM
  • 1=1 means u have no conditions for the fetch. isnull(your_column,' ')=Isnull(@your_val,' ') can avoid some problem
    Many Thanks & Best Regards, HuaMin Chen
    • Marked as answer by chinna_82 Thursday, September 08, 2011 6:39 AM
    Thursday, September 08, 2011 6:27 AM
  • Thanks all for the explanation. Really appreciate it. Thank you.
    Thursday, September 08, 2011 6:39 AM