none
Conditional filtering

    Question

  • Hi there,

    I am writing a query that should return all indices or non-clustered ones only based on a bit (boolean) parameter(@IncrementalLoad). I had done something similar in the past using a combination of WHERE and CASE statement but cannot recall it anymore.

    This is what I have so far:

    	DECLARE	
    			@databasename VARCHAR(50) = 'DBName',
    			@tablename VARCHAR(50) = 'TableName',
    			@val BIT = 1,
    			@IncrementalLoad BIT = 0
    --	=========================
    --	Setting parameters
    --	=========================
    
    	DECLARE @Result int,
    			@statement VARCHAR(8000),
    			@Mode VARCHAR(7)
    
    	IF @val = 1
    		SET @Mode = 'REBUILD'
    	ELSE
    		SET @Mode = 'DISABLE'
    --	=============================
    --	Building query
    --	=============================
    
    				SELECT 'ALTER INDEX ' + I.name + ' ON ' + T.name + ' ' + @Mode as statement
    				  FROM sys.indexes I
    				  JOIN sys.tables T
    					ON I.object_id = T.object_id
    				 WHERE T.name = @tablename
    				   AND 1 = (Case when @IncrementalLoad = 1 and I.type_desc = 'NONCLUSTERED' THEN 1
    							 else 0
    						END)

    Any help would be appreciated.

    Thank you

    Friday, January 11, 2019 8:01 PM

Answers

  • AND I.type_desc = (CASE WHEN @IncrementalLoad = 1 THEN 'NONCLUSTERED' ELSE I.type_desc END)

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Tshindaye Friday, January 11, 2019 8:51 PM
    Friday, January 11, 2019 8:46 PM

All replies

  • Since @IncrementalLoad = 0, your query returns nothing. What is @IncrementalLoad for?

    A Fan of SSIS, SSRS and SSAS

    Friday, January 11, 2019 8:24 PM
  • When the @IncrementalLoad value is set to 0, I want to see all indices on the table. When it is set to 1, It want it to see the NONCLUSTERED indices  only.

    In other words, the @IncrementalLoad parameter is used to conditionally restrict the values returned by the query.

    Thank you Guoxiong


    • Edited by Tshindaye Friday, January 11, 2019 8:29 PM
    Friday, January 11, 2019 8:27 PM
  • DECLARE
    @databasename VARCHAR(50) = 'Test',
    @tablename VARCHAR(50) = 'Product',
    @val BIT = 1,
    @IncrementalLoad BIT = 1
    -- =========================
    -- Setting parameters Question
    --When the @IncrementalLoad value is set to 0, I want to see all indices on the table. When it is set to 1, 
    --I want it to see the NONCLUSTERED only.
    -- =========================

    DECLARE @Result int,
    @statement VARCHAR(8000),
    @Mode VARCHAR(7)

    IF @val = 1
    SET @Mode = 'REBUILD'
    ELSE
    SET @Mode = 'DISABLE'
    -- =============================
    -- Building query
    -- =============================

    SELECT 'ALTER INDEX ' + I.name + ' ON ' + T.name + ' ' + @Mode as statement
      FROM sys.indexes I
      JOIN sys.tables T
    ON I.object_id = T.object_id
    WHERE  I.type_desc  = (Case when @IncrementalLoad = 0 then I.type_desc else  'NONCLUSTERED' end)
    and i.name is not null


    mohammad waheed

    Friday, January 11, 2019 8:36 PM
  • It does not work Mohammad.

    Toggling the value of @IncrementalLoad between 1 and 0 does not product any changes.

    You may be able to run the query and see for yourself.

    Thanks for trying to help.

    Friday, January 11, 2019 8:43 PM
  • AND I.type_desc = (CASE WHEN @IncrementalLoad = 1 THEN 'NONCLUSTERED' ELSE I.type_desc END)

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Tshindaye Friday, January 11, 2019 8:51 PM
    Friday, January 11, 2019 8:46 PM
  • Thank you so much Guoxiong.

    It did it !

    Friday, January 11, 2019 8:52 PM