How to use a dynamic WHERE clause in my SQL CTE query?

Question

I've a question about my SQL CTE construction. I'm working with Azure Data Factory and a Stored Procedure in my database. What I want to do is:

Return my data from my view to Azure Data Factory in JSON format.
Return the data filtered dynamically in the WHERE clause based on my ObjectCode in my view in JSON format. -> To test step 2 I tried with a statical declared ObjectCode

But the single quote does not work right in the WHERE clause. I tried some things with REPLACE, CHAR(39) and double the quotes. Like they said here, here and here

Step 1 I've finished succesfull with this code:

BEGIN

            DECLARE @TABLE TABLE(RESULT NVARCHAR(MAX))
            DECLARE @QUERY NVARCHAR(MAX) = '

            ;WITH x(Params) as 
            (
            SELECT * FROM [Schema].[View] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )
            Select * from x  
            '
            Insert @TABLE
            EXEC (@QUERY)

            Select ',"pipelineParameters": ' + LEFT(RESULT,LEN(RESULT)-1) + '}' as Params from @TABLE;


        END

This query above gave me the right result. But, now I need to make a change for Step 2. I need to filter with the WHERE clause in the query.

So, I tried:

DECLARE @TABLE TABLE(RESULT NVARCHAR(MAX))
            DECLARE @ObjectCode NVARCHAR(MAX) = 'Objectname'
            DECLARE @QUERY NVARCHAR(MAX) = '

            ;WITH x(Params) as 
            (
            SELECT * FROM [Schema].[View] 
            WHERE Objectcode = REPLACE(@ObjectCode, '''', '')
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )
            Select * from x  
            '
            Insert @TABLE
            EXEC (@QUERY)
            Select ',"pipelineParameters": ' + LEFT(RESULT,LEN(RESULT)-1) + '}' as Params from @TABLE;

But when I run these query I get this error:

Does anyone know what I can improve here so I can get it working?

To pass parameters to dynamic SQL use sp_executesql https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15 — David Browne - Microsoft, Apr 22 '20 at 18:39
What are you trying to accomplish? Sample data and desired results would help. — Gordon Linoff, Apr 22 '20 at 19:36

score 0 · Accepted Answer · answered Apr 22 '20 at 19:41

Does this do what you want?

Insert @TABLE
    exec sp_executesql @QUERY, N'@ObjectCode nvarchar(max)', @ObjectCode=@ObjectCode;

As written, I would expect a syntax error when the query is run because of this WHERE clause:

WHERE Objectcode = REPLACE(@ObjectCode, '', ')

I think you intend:

DECLARE @QUERY NVARCHAR(MAX) = '
WITH x(Params) as  (
      SELECT *
      FROM [Schema].[View] 
      WHERE Objectcode = REPLACE(@ObjectCode, '''''''', '''')
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
     )
Select * 
from x';

Quoting things in dynamic SQL is always verbose. You can print out @Query to see if it is really what you intend.

'

score 0 · Answer 2 · answered Apr 23 '20 at 11:33

0

Thanks Gordon, the first one works for me:

Insert @TABLE
    exec sp_executesql @QUERY, N'@ObjectCode nvarchar(max)', @ObjectCode=@ObjectCode;

answered Apr 23 '20 at 11:33

haassiej

55
1
10

How to use a dynamic WHERE clause in my SQL CTE query?

2 Answers2