DSQL Syntax ETL Script

I am having problems with a ETL script that is syntactically correct but does not run; it is the where clause variable which uses data from a metadata table. I've redacted all but the relevant code:


DECLARE @table VARCHAR(200) = 'MyTable'
DECLARE @w_clause NVARCHAR(MAX)
DECLARE @j_clause NVARCHAR(MAX)

-- Get non-primary key column names
DROP TABLE IF EXISTS #temp0
SELECT [Name], FieldType
INTO #temp0 
FROM dbo.metadata
WHERE [Table] = @table AND 
      ETL_Active = 1 AND 
      [Name] IS NOT NULL AND 
      PrimaryKey <> 1


-- Get primary key column names
DROP TABLE IF EXISTS #temp1
SELECT [Name]
INTO #temp1
FROM dbo.metadata
WHERE [Table] = @table AND
      ETL_Active = 1 AND
      [Name] IS NOT NULL AND
      PrimaryKey = 1


SELECT @w_clause = COALESCE(@w_clause+' OR ','') + 'COALESCE(prd.'+ [Name] +',' + CASE WHEN FieldType IN('char','varchar', 'nvarchar', 'nchar') THEN '''' ELSE 0 END +')' FROM #temp0
PRINT @w_clause
SELECT @j_clause = COALESCE(@j_clause+' AND ','') + 'prd.'+ [Name] + ' = ' + 'stg.' + [Name] FROM #temp1
PRINT @j_clause

The error I'm getting:

Msg 245, Level 16, State 1, Line xx
Conversion failed when converting the varchar value ')' to data type int.

This occurs when I attempt the COALESCE(prd dynamic SQL assignment to the variable for the WHERE clause. This has me stomped so any help is appreciated. Thanks.

Solution

You should use CONCAT instead of + for string concatenation. With + you have to cast both arguments to string, and replace NULL with ''. CONCAT does both for you.

That should be:

SELECT @w_clause = COALESCE(@w_clause+' OR ','') + 'COALESCE(prd.'+ [Name] +',' + CASE 
WHEN FieldType IN('char','varchar', 'nvarchar', 'nchar') THEN '''' ELSE '0' END +')'

SELECT @w_clause = concat(COALESCE(@w_clause+' OR ',''), 'COALESCE(prd.', [Name], ',',  CASE WHEN FieldType IN('char','varchar', 'nvarchar', 'nchar') THEN '''' ELSE '0' END ')' )