title: sp_executesql not running sql using 'with' clause
body: <p>in work we use company-based test-framework. You just save your testing query into specific column in specific table, then  execute the stored procedure, which calls sp_executesql to execute the query, then it logs the results and that all.
  Today I've build test query using with clause. After executing query within framework I get this error everytime I run query with WITH statement.:
  " Incorrect syntax near the keyword 'With'". 
  But when I run the query just as sql select I am getting the results, so the query works fine. I am just not able to run query using sp_executesql statement.
  My question is, is it even possible to run the query using with clause within sp_executesql command? Or how can I handle this situation? Any tips?</p>
  
  <p>This is my query:</p>
  
  <pre><code>With source_state as (
  select 
    DISPLAY_VALUE as DISPLAY_VALUE 
   ,FL_SIEBEL as FL_SIEBEL
   ,ID_CONSENT_ACTION as ID_CONSENT_ACTION 
   ,LANG_INDEPENDENT_CODE as LANG_INDEPENDENT_CODE
  from [dbo].[DM_CONSENT_ACT]
  ),
  target_state as (
   select 
    DISPLAY_VALUE as DISPLAY_VALUE 
   ,FL_SIEBEL as FL_SIEBEL
   ,ID_CONSENT_ACTION as ID_CONSENT_ACTION 
   ,LANG_INDEPENDENT_CODE as LANG_INDEPENDENT_CODE
   --select *
  from [dbo].[DM_LD_CONSENT_ACT2]
  where isnull(dt_eff_to,@param1)&gt;@param2
  )
  , pocet  as 
  (SELECT * FROM source_state   EXCEPT  SELECT  t.* FROM target_state as t  )
  , pocet2 as 
  (SELECT * FROM target_state   EXCEPT  SELECT  t.* FROM source_state as t  )
  select (select COUNT(*) from pocet) + (select COUNT(*) from pocet2)
  </code></pre>
  
  <p>This is part of stored procedure where I am running the code:</p>
  
  <pre><code> DECLARE tables_cursor_params CURSOR  
         FOR  
         SELECT NAME   
         FROM TF_PARAMS
  
         OPEN tables_cursor_params
  
         DECLARE @Name varchar(100)  
  
         FETCH NEXT FROM tables_cursor_params INTO @Name
         WHILE (@@FETCH_STATUS &lt;&gt; -1)  
         BEGIN  
  
          declare @getParam nvarchar(max)
          declare @sqlGetParam nvarchar(max) = 'select @getParam = VALUE from [dbo].[TF_PARAMS] where NAME = '''+@Name+''''
          print @sqlGetParam
          declare @Param varchar(max)
          exec sp_executesql @sqlGetParam, N'@getParam varchar(max) out', @Param out
          set @v_sql_a_replaced = replace(@v_sql_a_replaced, '@'+@Name, ''''+@Param+'''')
          print @v_sql_a_replaced
            FETCH NEXT FROM tables_cursor_params INTO @Name 
         END
  
         CLOSE tables_cursor_params  
         DEALLOCATE tables_cursor_params
  </code></pre>
  
  <p>Cheers!
  Jozef</p>