1

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?

This is my query:

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)>@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)

This is part of stored procedure where I am running the 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 <> -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

Cheers! Jozef

Jozef Čech
  • 11
  • 2
  • 2
    Please show your procedure and your query. – sticky bit Jun 02 '18 at 17:35
  • Make sure the preceding statement in the batch is terminated with a semicolon. If not, it's a bug in your testing framework as not using semicolon statement terminators is deprecated. – Dan Guzman Jun 02 '18 at 17:36
  • Saving dynamic SQL in a table sounds like a bad idea. What if someone altered the code and put something malicious in there? Sounds like what the otherssaid though, someone isn't ended their SQL statements with a semi-colon (`;`). Not terminating them is deprecated. – Thom A Jun 02 '18 at 17:44
  • I've tried to put semicolond at the beggining of the query so it looked like "; with..." but this still did not work for me – Jozef Čech Jun 02 '18 at 17:52
  • Oh gods... Why are you parametrising that dynamic SQL for get the paramete rvalue? Imagin eif I passed the value `'''-- DROP TABLE dbo.[TF_PARAMS];--'`. Also, what is the value of variable before you try to execute it? SQL like the following works fine; so there's a syntax in there, somewhere. `DECLARE @SQL nvarchar(MAX) = N'WITH CTE AS( SELECT @I AS param) SELECT * FROM CTE;' EXEC sp_executesql @SQL, N'@i int',@i = 1;` Notice I didn't put a semicolon at the end of the `DECLARE` but `sp_executesql` still worked. – Thom A Jun 02 '18 at 17:54
  • In fact, after looking at that SQL, why does it need to be dynamic? I can see no reason for it to be; apart from to open yourself up to SQL injection that is. – Thom A Jun 02 '18 at 17:59

0 Answers0