时间戳

首页 > 解决方案 > 将静态 SQL 转换为动态

sql - 将静态 SQL 转换为动态

问题描述

我有一个静态 SQL 查询，它找出空白计数和数据类型（无论是数字还是字母数字等）。以下是查询：

SELECT
case when Pattern = '' then 'BLANK' else Pattern end AS Pattern,
LEN(case when Pattern = '' then 'BLANK' else Pattern end) Length,
COUNT(*) AS Count FROM
     ( SELECT REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(      
      REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( 
      REPLACE( REPLACE(REPLACE( REPLACE( 
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE    
      (REPLACE(REPLACE(REPLACE(REPLACE(UPPER(col_name),'A','A'),'B','A') ,'C','A') ,'D','A') 
      ,'E','A'),'F','A') ,'G','A') ,'H','A') ,'I','A'),'J','A') ,'K','A') ,'L','A') ,'M','A')     
      ,'N','A') ,'O','A') ,'P','A')
      ,'Q','A'),'R','A') ,'S','A') ,'T','A') ,'U','A') ,'V','A') ,'W','A') ,'X','A') ,'Y','A')    
      ,'Z','A'),'8','N'),'7','N'),'6','N'),'5','N'),'4','N'),'3','N')   
      ,'2','N'),'1','N'),'0','N'),'9','N')  
    AS Pattern FROM table_name) A  GROUP BY Pattern;

如何将其转换为动态 SQL，以便我可以遍历表的所有列？

编辑

假设我有一张下表

CREATE TABLE Data (
   Column1 varchar(50),
   Column2 varchar(50),
   Column3 varchar(50),
   Column4 varchar(50),
   )
INSERT INTO Data
   (Column1, Column2, Column3, Column4)
VALUES 
   (NULL, NULL, 'ABC123', 'abc '),
   ('xyz', NULL, 'MNO300', 'XYZ123 ')

现在我想要如下输出：

Count_Pat_1 --> Count is of only one data type i.e. either Alphabetic or 
Numeric
Count_Part_2 --> Count is of mixed data type i.e. contains both alphabet and 
number

Col_name    Count_Pat_1    Count_Pat_2  Blank
Column1          1               0       1        
Column2          0               0       2
Column3          0               2       0
Column4          1               1       0

如何生成上表？显然，我需要求助于动态 SQL，其中sql需要将整个字符串设置为变量。

DECLARE @sql nvarchar(max)
SET @sql = N''

SELECT @sql = @sql + CASE WHEN @stm = N'' THEN N'' ELSE N',' END +
 '('+
 '''' + c.name + ''', '+
 'CASE '+
    'WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN 1 ' +
    'ELSE 0 ' +
 'END, ' +
 'CASE '+
    'WHEN ' + QUOTENAME(c.name) + ' IS NOT NULL THEN LEN(' + 
  QUOTENAME(c.name) + ') - ** the above SQL script ***
  FROM sys.columns c
  JOIN sys.tables t ON c.object_id = t.object_id
  WHERE t.name = 'Data'

但是在 ** 指示的位置插入上述 SQL 脚本将无法正确给出输出。

因此，任何提示将不胜感激。SQL Server 版本：2016

标签： sqlsql-server

解决方案

使用动态 SQL 的诀窍是首先让它作为静态 SQL 工作，然后将其转换为动态 SQL。一些注意事项：

  1. 要获得多行，每列 1 考虑使用union all.
  2. 要获得 a 的计数，case您需要使用sum.
  3. 我使用了一些可能的逻辑来获得count一种数据类型或混合数据类型，但您可能会发现您需要更精确的逻辑。
    DECLARE @Sql NVARCHAR(MAX) = '';

    SELECT @Sql = @Sql
      + CASE WHEN @Sql = '' THEN 'SELECT ' ELSE ' UNION ALL SELECT ' END
      + '''' + c.[name] + ''' ColumnName'
      + ', SUM(CASE WHEN ' + QUOTENAME(c.[name]) + ' IS NOT NULL AND ' + QUOTENAME(c.[name]) + ' NOT LIKE ''%[^0-9.]%'' OR ' + QUOTENAME(c.[name]) + ' NOT LIKE ''%[0-9.]%'' THEN 1 ELSE 0 END) [Count_Pat_1]'
      + ', SUM(CASE WHEN ' + QUOTENAME(c.[name]) + ' IS NOT NULL AND ' + QUOTENAME(c.[name]) + ' LIKE ''%[^0-9.]%'' AND ' + QUOTENAME(c.[name]) + ' LIKE ''%[0-9.]%'' THEN 1 ELSE 0 END) [Count_Pat_2]'
      + ', SUM(CASE WHEN ' + QUOTENAME(c.[name]) + ' IS NULL THEN 1 ELSE 0 END) [Blank]'
      + ' FROM Data'
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.[name] = 'Data';

    -- print @Sql; -- For debugging
    exec sp_executesql @Sql;

按要求退货：

ColumnName  | Count_Pat_1   | Count_Pat_2   | Blank
---------------------------------------------------
Column1     | 1             | 0             | 1
Column2     | 0             | 0             | 2
Column3     | 0             | 2             | 0
Column4     | 1             | 1             | 0

推荐阅读