title: Dynamic queries and Dynamic Where clauses Solution
body: <p>This week, I found myself in need of some dynamic queries. Now, dynamic queries and dynamic where clauses are nothing new and well documented all over the web.  Yet, I needed something more. I needed a fluid way of pulling new where fields to the client and allowing the users to make as many filters as needed. Even have multiple filters on a single field. Even more so, I needed to have access to all the possible operators within SQL server. The following is code is one way to make this happen. I will attempt to point out highlights of the code with the complete code at the bottom.</p>
  
  <p>Hope you enjoy the code. </p>
  
  <p>REQUIREMENTS</p>
  
  <ol>
  <li>The solution will never allow SQL injections. (No exec(command) can be used)</li>
  <li>The caller of the stored procedure could be anything.</li>
  <li>The data set must come from a Stored Procedure.</li>
  <li>Any field can be filtered as many times as needed, with just about any operation.</li>
  <li>Any combination of filters should be allowed.</li>
  <li>The stored procedure should allow for mandatory parameters</li>
  </ol>
  
  <p>First, let us look over the parameters. </p>
  
  <pre><code>CREATE PROCEDURE [dbo].[MyReport]
  -- Add the parameters for the stored procedure here
  @p_iDistributorUID  INT,        --    manditory
  @p_xParameters      XML = null  --optional parameters (hostile)
  </code></pre>
  
  <p>The first parameter must always be sent, in this demo we have a distributor id that must be sent in. The second parameter is an XML document. These are the “Dynamic Where Clauses” and we consider these potential sql injections, or as I perceive this parameter as hostile.</p>
  
  <pre><code>&lt;root&gt;
  &lt;OrFilters&gt;
      &lt;AndFilter Name="vcinvoicenumber" Operator="2" Value="inv12"/&gt;
      &lt;AndFilter Name="vcID" Operator="1" Value="asdqwe"/&gt;
  &lt;/OrFilters&gt;
  &lt;OrFilters&gt;
      &lt;AndFilter Name="iSerialNumber" Operator="1" Value="123456"/&gt;
  &lt;/OrFilters&gt;
  </code></pre>
  
  <p></p>
  
  <p>NAME= field name(you could just use the object_id if you want to obfuscate) 
  OPERATOR = SQL operators such as &lt;,>,=,like,ect.
  VALUE is what the users has entered.</p>
  
  <p>Here is what the final code would look like.</p>
  
  <pre><code>Select *
  FROM someTable
  Where (
  vcinvoicenumber like ‘inv12%’
  and vcID = ‘asdqwe’
  ) 
  Or
  (
  iSerialNumber   = ‘123456’  
  )
  </code></pre>
  
  <p>First thing is to find out how many “OrFilters” tags there are.</p>
  
  <pre><code>SELECT @l_OrFilters = COUNT(1)
  FROM   @p_xParameters.nodes('/root/OrFilters') Tab(Col)
  </code></pre>
  
  <p>Next we need a temp table to hold the values in the XML doc.</p>
  
  <pre><code>CREATE TABLE #temp
  (keyid int IDENTITY(1,1) NOT NULL,value varchar(max))
  </code></pre>
  
  <p>We now create a cursor for the first “OrFilters”tag.</p>
  
  <pre><code>DECLARE OrFilter_cursor CURSOR LOCAL
  FOR 
  SELECT Tab.Col.value('@Name','varchar(max)') AS Name
      ,Tab.Col.value('@Operator','Smallint') AS Operator
      ,Tab.Col.value('@Value','varchar(max)') AS Value
      FROM   @p_xParameters.nodes('/root/OrFilters[sql:variable("@l_OrFilters")]/AndFilter') Tab(Col)
  </code></pre>
  
  <p>To make sure we have a valid field, we check against the system tables.</p>
  
  <pre><code>SELECT @l_ParameterInName = [all_columns].Name 
  ,@l_ParameterDataType= [systypes].Name
  ,@l_ParameterIsVariable= Variable
  ,@l_ParameterMax_length=max_length
  ,@l_ParameterpPrecision=precision
  ,@l_ParameterScale =[all_columns].scale
   FROM [AprDesktop].[sys].[all_views]
  INNER JOIN [AprDesktop].[sys].[all_columns]
      ON [all_views].object_id = [all_columns].object_id
  INNER JOIN [AprDesktop].[sys].[systypes]
      ON [all_columns].system_type_id = [systypes].xtype
  WHERE [all_views].name = 'vw_CreditMemo_Lists'
  and [all_columns].Name= @l_Name
  </code></pre>
  
  <p>Now we save the parameter to the temp table</p>
  
  <pre><code>IF @@ROWCOUNT = 1   
  BEGIN
  INSERT INTO #temp (value) SELECT @l_Value
  SET @l_FilterKey = @@IDENTITY
  .
  .   
  .
  </code></pre>
  
  <p>We make a call to a function that will actually build the where clauses.</p>
  
  <pre><code>SET @l_TemporaryWhere +=  
  dbo.sfunc_FilterWhereBuilder2(@l_Operator
  ,@l_ParameterInName
  ,@l_TemporaryWhere
  ,CAST(@l_FilterKey AS VARCHAR(10))
  ,@l_ParameterDataType
  ,@l_ParameterVariable)
  </code></pre>
  
  <p>Looking at this Function, you can see we used a case statement to genereate the where clause string.</p>
  
  <pre><code>set @l_CastToType = ' CAST( VALUE as ' + @p_DataType + @p_PrecisionScale + ') '
  set @l_CastToString = ' CAST( '+@p_Field+' as VARCHAR(MAX)) '
  
  -- Add the T-SQL statements to compute the return value here
  SELECT @l_Return =    
      CASE 
  --EQUAL
  --ex: vcUID = (select  value FROM #temp where keyid = 1)
  WHEN @p_Command = 1 
      THEN @p_Field + ' = (select  '+@l_CastToType+' FROM #temp where keyid = ' + @p_KeyValue + ')' 
  
  --BEGIN WITH
  --ex:vcInvoiceNumber LIKE (select value+'%' FROM #temp where keyid = 2)
  WHEN @p_Command = 2 
      THEN @l_CastToString +' LIKE (select value+'+ QUOTENAME('%','''') +' FROM #temp where keyid = ' + @p_KeyValue + ')'
  .
  .
  .
  </code></pre>
  
  <p>And finally call the sp_execute.</p>
  
  <pre><code>EXECUTE sp_executesql @l_SqlCommand ,@l_Parameters, @p_iDistributorUID
  </code></pre>
  
  <p>CALLING CODE</p>
  
  <pre><code>DECLARE @return_value int
  
  DECLARE @myDoc xml
  SET @myDoc = 
  '&lt;root&gt;
  &lt;OrFilters&gt;
      &lt;AndFilter Name="vcinvoicenumber" Operator="1" Value="123"/&gt;
  &lt;/OrFilters&gt;
  
  &lt;/root&gt;'
  
  EXEC    @return_value = [dbo].[spp_CreditMemo_Request_List_v2]
      @p_siShowView = 1,
      @p_iDistributorUID = 3667,
      @p_xParameters = @myDoc
  
  SELECT  'Return Value' = @return_value
  </code></pre>
  
  <p>MAIN STORED PROCEDURE</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[MyReport]
      -- Add the parameters for the stored procedure here
      @p_iDistributorUID          INT                 ,   --manditory
      @p_xParameters              XML         = null      --optional parameters(hostile)
  AS
  BEGIN
  
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      DECLARE @l_TemporaryWhere       NVARCHAR(MAX)
  
      -- declare variables 
      DECLARE @l_SqlCommand           NVARCHAR(MAX)
      DECLARE @l_Parameters           NVARCHAR(MAX)
      DECLARE @l_WhereClause          NVARCHAR(MAX)
  
      DECLARE @l_OrFilters            INT
  
      --cursor variables
      DECLARE @l_Name                 VARCHAR(MAX)
      DECLARE @l_Operator             SMALLINT
      DECLARE @l_Value                VARCHAR(MAX)
  
      --variables from the database views 
      DECLARE @l_ParameterInName      NVARCHAR(128)
      DECLARE @l_ParameterDataType    NVARCHAR(128)
      DECLARE @l_ParameterIsVariable  BIT
      DECLARE @l_ParameterMax_length  SMALLINT
      DECLARE @l_ParameterpPrecision  TINYINT
      DECLARE @l_ParameterScale       TINYINT
  
      --the variable that holds the latest @@identity
      DECLARE @l_FilterKey INT
  
  
      --init local variables
      SET @l_SqlCommand   =''
      SET @l_Parameters   =''
      SET @l_WhereClause  =''
  
  
      BEGIN TRY
  
          --verify manditory variables        
          if @p_iDistributorUID is null
              raiserror('Null values not allowed for @p_iDistributorUID', 16, 1)
  
  
  
          --Build the base query
          -- only the fields needed in the tile should be selected
          SET @l_SqlCommand = 
              ' SELECT * ' +
              ' FROM vw_Lists ' 
  
  
  
          --how many "OR" filters are there
          SELECT @l_OrFilters = COUNT(1)
          FROM   @p_xParameters.nodes('/root/OrFilters') Tab(Col)
  
  
          --create a temp table to 
          --hold the parameters to send into the sp
          CREATE TABLE #temp
          (
              keyid int IDENTITY(1,1) NOT NULL,value varchar(max)
          )
  
  
          --Cycle through all the "OR" Filters
          WHILE @l_OrFilters &gt; 0
          BEGIN
              SET @l_TemporaryWhere = '';
  
              --Create a cursor of the Next "OR" filter
              DECLARE OrFilter_cursor CURSOR LOCAL
              FOR 
                  SELECT Tab.Col.value('@Name','varchar(max)') AS Name
                      ,Tab.Col.value('@Operator','Smallint') AS Operator
                      ,Tab.Col.value('@Value','varchar(max)') AS Value
                  FROM   @p_xParameters.nodes('/root/OrFilters[sql:variable("@l_OrFilters")]/AndFilter') Tab(Col)
  
              OPEN OrFilter_cursor
              FETCH NEXT FROM OrFilter_cursor 
              INTO @l_Name, @l_Operator,@l_Value  
  
              WHILE    @@FETCH_STATUS = 0
              BEGIN
  
                  --verify the parameter actual exists
                  --  and get parameter details
                  SELECT @l_ParameterInName = [all_columns].Name 
                      ,@l_ParameterDataType= [systypes].Name
                      ,@l_ParameterIsVariable= Variable
                      ,@l_ParameterMax_length=max_length
                      ,@l_ParameterpPrecision=precision
                      ,@l_ParameterScale =[all_columns].scale
                  FROM [AprDesktop].[sys].[all_views]
                      INNER JOIN [sys].[all_columns]
                          ON [all_views].object_id = [all_columns].object_id
                      INNER JOIN [sys].[systypes]
                          ON [all_columns].system_type_id = [systypes].xtype
                  WHERE [all_views].name = 'vw_CreditMemo_Lists'
                      and [all_columns].Name= @l_Name
  
                  --if the paremeter exists, create a where clause
                  --  if the parameters does not exists, possible injection
                  IF @@ROWCOUNT = 1   
                  BEGIN
  
                      --insert into the temp table the parameter value
                      --NOTE: we have turned in the @@identity as the key
                      INSERT INTO #temp (value) SELECT @l_Value
                      SET @l_FilterKey = @@IDENTITY
  
  
                      -- if the parameter is variable in length, add the length
                      DECLARE @l_ParameterVariable VARCHAR(1000)
                      IF @l_ParameterIsVariable = 1
                      BEGIN
                          SET @l_ParameterVariable ='(' + CAST(@l_ParameterMax_length as VARCHAR(MAX))  + ') '
                      END
                      ELSE
                      BEGIN
                          SET @l_ParameterVariable = ''
                      END
  
                      -- create the where clause for this filter
                      SET @l_TemporaryWhere +=  
                          dbo.sfunc_FilterWhereBuilder2(@l_Operator
                                                       ,@l_ParameterInName
                                                       ,@l_TemporaryWhere
                                                       ,CAST(@l_FilterKey AS VARCHAR(10))
                                                       ,@l_ParameterDataType
                                                       ,@l_ParameterVariable)
  
  
                  END
  
                  FETCH NEXT FROM OrFilter_cursor 
                  INTO @l_Name, @l_Operator,@l_Value  
  
              END
  
              -- clean up the cursor
              CLOSE OrFilter_cursor
              DEALLOCATE OrFilter_cursor
  
              --add the and filers
              IF @l_TemporaryWhere != ''
              BEGIN
  
                  --if the where clause is not empty, we need to add an OR
                  IF @l_WhereClause != ''
                  BEGIN
                      SET @l_WhereClause += ' or ';
                  END
  
                  --add temp to where clause including the 
                  SET @l_WhereClause += '(' + @l_TemporaryWhere + ')';
  
              END
  
              --get the next AND set
              SET @l_OrFilters = @l_OrFilters - 1
  
          END
  
          --generate the where clause
          IF @l_WhereClause != ''
          BEGIN
              SET @l_WhereClause ='('+ @l_WhereClause + ') AND '
          END
  
          --add in the first mandatory parameter          
          SET @l_WhereClause  += '  vw_CreditMemo_Lists.iDistributorUID = @l_iDistributorUID ' 
          SET @l_Parameters   += '@l_iDistributorUID int'
  
  
          --do we need to attach the where clause
          if @l_WhereClause IS NOT NULL AND RTRIM(LTRIM(@l_WhereClause)) != ''
          BEGIN
              SET @l_SqlCommand += ' WHERE '+ @l_WhereClause;
          END
  
  
          print @l_SqlCommand
  
          --query for the data
          EXECUTE sp_executesql @l_SqlCommand ,@l_Parameters, @p_iDistributorUID
  
      END TRY
  
      BEGIN CATCH
  
          DECLARE @ErrorUID int;
          DECLARE @ErrorMessage NVARCHAR(4000);
          DECLARE @ErrorSeverity INT;
          DECLARE @ErrorState INT;
  
          SELECT 
              @ErrorMessage = ERROR_MESSAGE(),
              @ErrorSeverity = ERROR_SEVERITY(),
              @ErrorState = ERROR_STATE();
  
  
          --write the to stored procedure log 
          EXEC @ErrorUID = spp_Errors_CreateEntry @l_SqlCommand 
  
  
          -- Use RAISERROR inside the CATCH block to return error
          -- information about the original error that caused
          -- execution to jump to the CATCH block.
          RAISERROR (@ErrorUID, -- Message text.
                     @ErrorSeverity, -- Severity.
                     @ErrorState -- State.
                     );
  
  
          IF(CURSOR_STATUS('LOCAL','OrFilter_cursor') &gt;= 0)
          BEGIN
              CLOSE OrFilter_cursor
          END     
  
          IF(CURSOR_STATUS('LOCAL','OrFilter_cursor') = -1)
          BEGIN
              DEALLOCATE OrFilter_cursor
          END     
  
  
      END CATCH
  
      return
  
  END
  </code></pre>
  
  <p>FUNCTION</p>
  
  <pre><code>ALTER FUNCTION [dbo].[sfunc_FilterWhereBuilder2]
  (
      @p_Command          SMALLINT                ,
      @p_Field            VARCHAR(1000)           ,
      @p_WhereClause      VARCHAR(MAX)            ,
      @p_KeyValue         VARCHAR(10)             ,
      @p_DataType         VARCHAR(100)    = NULL  ,
      @p_PrecisionScale   VARCHAR(100)    = NULL
  
  )
  RETURNS VARCHAR(MAX)
  AS
  BEGIN
      -- Declare the return variable here
      DECLARE @l_Return       VARCHAR(MAX)
      DECLARE @l_CastToType   VARCHAR(4000)
      DECLARE @l_CastToString VARCHAR(MAX)
  
      set @l_CastToType = ' CAST( VALUE as ' + @p_DataType + @p_PrecisionScale + ') '
      set @l_CastToString = ' CAST( '+@p_Field+' as VARCHAR(MAX)) '
  
      -- Add the T-SQL statements to compute the return value here
      SELECT @l_Return =    
          CASE 
              --EQUAL
              --ex: vcBurnUID = (select  value FROM #temp where keyid = 1)
              WHEN @p_Command = 1 
                  THEN @p_Field + ' = (select  '+@l_CastToType+' FROM #temp where keyid = ' + @p_KeyValue + ')' 
  
              --BEGIN WITH
              --ex:vcInvoiceNumber LIKE (select value+'%' FROM #temp where keyid = 2)
              WHEN @p_Command = 2 
                  THEN @l_CastToString +' LIKE (select value+'+ QUOTENAME('%','''') +' FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --END WITH
              --ex:vcInvoiceNumber LIKE (select '%'+value FROM #temp where keyid = 2)
              WHEN @p_Command = 4 
                  THEN @l_CastToString +' LIKE (select '+ QUOTENAME('%','''') +'+value FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --END WITH
              --ex:vcInvoiceNumber LIKE (select '%'+value+'%' FROM #temp where keyid = 2)
              WHEN @p_Command = 8 
                  THEN @l_CastToString +' LIKE (select '+ QUOTENAME('%','''') +'+value+'+ QUOTENAME('%','''') +' FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --greater than
              --ex: iSerialNumber &gt; (select  CAST(value as INT) FROM #temp where keyid = 1)
              WHEN @p_Command = 16 
                  THEN @p_Field +' &gt; (select  '+@l_CastToType+' FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --greater than equal
              --ex: iSerialNumber &gt;= (select  CAST(value as INT) FROM #temp where keyid = 1)
              WHEN @p_Command = 32 
                  THEN @p_Field +' &gt;= (select  '+@l_CastToType+' FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --Less than
              --ex: iSerialNumber &lt; (select  CAST(value as INT) FROM #temp where keyid = 1)
              WHEN @p_Command = 64 
                  THEN @p_Field +' &lt; (select  '+@l_CastToType+' FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --less than equal
              --ex: iSerialNumber &lt;= (select  CAST(value as INT) FROM #temp where keyid = 1)
              WHEN @p_Command = 128 
                  THEN @p_Field +' &lt;= (select  '+@l_CastToType+' FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --less than equal
              --ex: iSerialNumber != (select  CAST(value as INT) FROM #temp where keyid = 1)
              WHEN @p_Command = 256 
                  THEN @p_Field +' != (select  '+@l_CastToType+' FROM #temp where keyid = ' + @p_KeyValue + ')'
  
              --default to an empty string
              ELSE ''
  
          END 
  
          if @l_Return != '' and LEN(@p_WhereClause) &gt; 1
          begin
              set @l_Return =  ' AND ' + @l_Return
          end
  
      -- Return the result of the function
      RETURN @l_Return
  
  END
  </code></pre>