0

I am trying to create a dynamic query as below in SQL Server. But keep getting error as:

Msg 105, Level 15, State 1, Line 9
Unclosed quotation mark after the character string '%[^a-Z0-9]%'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '%[^a-Z0-9]%

I am not able to figure out how to resolve this. I am creating sample queries for test cases where tester have to just change few variables and don't have to touch the logic.

--Rule A.1.Summary :- Checking for Invalid Characters
/* The 'DECLARE' statements below are used to
define standard test parameters and their datatypes. The values for these
parameters will be updated for each of the DQ dimensions/KDEs being tested */
DECLARE @DQ_DIMENSION_RULE VARCHAR(100)
DECLARE @RULE_NO VARCHAR(100)
DECLARE @TABLE_NAME VARCHAR(100)
DECLARE @DATA_ATTRIBUTE VARCHAR(100)

/*The 'SET' statements below are used to
assign values to each of the test parameters declared above. 
The values will depend on the DQ dimension/KDE being tested.*/
SET @DQ_DIMENSION_RULE = 'Accuracy - Invalid Characters'
SET @RULE_NO = 'A.1'
SET @TABLE_NAME = 'TRANSACTIONS'
SET  @DATA_ATTRIBUTE = 'TRANSACTIONID'

DECLARE @sql nvarchar(max) = N'
SELECT 
    ' + QUOTENAME(@DQ_DIMENSION_RULE, '''') + N' AS [DQ_DIMENSION_RULE],
    ' + QUOTENAME(@RULE_NO, '''') + N' AS [RULE_NO],
    ' + QUOTENAME(@TABLE_NAME, '''') + N' AS [Table Name],
    ' + QUOTENAME(@DATA_ATTRIBUTE, '''') + N' AS [Column Name],
    A.* 
FROM ' + QUOTENAME(@TABLE_NAME) + N' A
WHERE '+ QUOTENAME(@DATA_ATTRIBUTE) + N' like ''%[^a-Z0-9]%'
;

EXEC sp_executesql @sql ;
Dale K
  • 25,246
  • 15
  • 42
  • 71
Rohit
  • 1
  • 2
  • 1
    While you are writing and testing, best is to add `PRINT @sql` before the `EXEC` then you can see the generated SQL – Charlieface Jun 21 '21 at 20:15

1 Answers1

1

You need closing quotes for the pattern string:

WHERE '+ QUOTENAME(@DATA_ATTRIBUTE) + N' like ''%[^a-Z0-9]%'''
-----------------------------------------------------------^
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786