Parasoft SOAtest 9.7 doesn’t support Common Table Expression (CTE) in SQL Server 2008 R2

If you have the following SQL query that uses Common Table Expression (CTE), you will see the error after trying to populate the data in SOAtest.

  

DECLARE @PolicyName varchar(20)='%Glucose%'

…

; WITH CTE as

(

  SELECT field1, field2

  from Policy PO  WITH(NOLOCK)

  WHERE field1 like @PolicyName

)

The following error shows up in XML data bank after running the test.

com.microsoft.sqlserver.jdbc.SQLServerException: Must declare the scalar variable "@PolicyName".

When I see this error, I feel so surprised because this SQL query is always working in SQL Server 2008 R2, but SOAtest complains about it using CTE.

How to solve this issue?  We need to declare this variable in SOAtest.

1. Double Click Scenario
2. Click Variables and add PolicyName like this

    3.Modify the following SQL query

; WITH CTE as

(

  SELECT field1, field2

  from Policy PO  WITH(NOLOCK)

  WHERE field1 like '${PolicyName}’

)

After running the test, the variable will be passed to SQL query in CTE and the test will pass without any issue.