根据通过字符串传递的列名创建动态SQL [英] Create dynamic SQL based on column names passed through a string

问题描述

我需要找出表A中存在的行和表B中缺少的行(使用LEFT JOIN)，其中表A和表B是两个具有相同结构但在不同架构中的表. 但是查询必须使用Dynamic SQL构造，并且用于执行JOIN的列存储在字符串中.如何从字符串中提取列名并使用它们动态构造以下查询:

I need to find out rows that are present in table A and missing from table B (using LEFT JOIN) wherein table A and table B are two tables with same structure but within different schema. But the query has to be constructed using Dynamic SQL and the columns that need to be used for performing JOIN are stored in a string. How to extract the column names from string and use them to dynamically construct below query :

数据库是Azure SQL Server

Database is Azure SQL Server

例如:

DECLARE @ColNames NVARCHAR(150) = 'col1,col2'

要基于ColNames中定义的列构造的查询:-

Query to be constructed based on columns defined in ColNames :-

SELECT *
FROM Table A
Left Join
Table B
ON A.col1 = B.col1
AND A.col2 = B.col2
AND B.col1 IS NULL AND B.col2 IS NULL

如果@ColNames中的列数更多，则SELECT语句需要满足所有列.

If the number of columns in @ColNames is more then the SELECT statement needs to cater for all the column.

推荐答案

在不知道完整上下文的情况下，请尝试以下操作:

Without knowing the full context, try this:

DECLARE @ColNames NVARCHAR(150) = 'col1,col2'
DECLARE @JoinContion NVARCHAR(MAX) = ''
DECLARE @WhereCondition NVARCHAR(MAX) = ''

SELECT @JoinContion += CONCAT('[a].', QUOTENAME(Value), ' = ', '[b].', QUOTENAME(Value), (CASE WHEN LEAD(Value) OVER(ORDER BY Value) IS NOT NULL THEN ' AND ' ELSE '' END))
    ,@WhereCondition += CONCAT('[a].', QUOTENAME(Value), ' IS NULL', (CASE WHEN LEAD(Value) OVER(ORDER BY Value) IS NOT NULL THEN ' AND ' ELSE '' END))
FROM STRING_SPLIT(@ColNames,N',')

SELECT @JoinContion, @WhereCondition

• String_Split :将输入字符串拆分为列
• 领导:确定在不是最后一行时是否需要AND关键字.
• String_Split: To split the input string into columns
• Lead: to determine if we need the AND keyword when it's not the last row.

请注意，与LEFT JOIN

这篇关于根据通过字符串传递的列名创建动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！