时间戳

首页 > 解决方案 > 如何在动态查询中使用整数变量

sql - 如何在动态查询中使用整数变量

问题描述

我创建了以下动态查询来检查特定属性的长度是否超过 50。我试图使长度大小也动态但低于错误。

Msg 207, Level 16, State 1, Line 7
Invalid column name '50'.

我提出的查询如下。我对 SQL 很陌生，并试图解决这个错误。谢谢！

--Rule A.5.Summary :- Length
/* 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)
DECLARE @LENGTH_SIZE INT

/*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 - Negative Values'
SET @RULE_NO = 'A.4'
SET @TABLE_NAME = 'TRANSACTIONS'
SET @DATA_ATTRIBUTE = 'TRANSACTIONID'
SET @LENGTH_SIZE = 50

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],
    case when SUM(CASE WHEN LEN(' + QUOTENAME(@DATA_ATTRIBUTE) + N') >' + QUOTENAME(@LENGTH_SIZE) + N' THEN 1 ELSE 0 END) > 0 then ''Y'' else ''N'' end as [Potential Issue(Y/N)]
    
FROM ' + QUOTENAME(@TABLE_NAME)

-- The data from 'SELECT' statement is being inserted into the summary table
--INSERT INTO summary
EXEC sp_executesql @sql;

标签： sqlsql-servertsqldynamic-sql

解决方案

您不应该放置QUOTENAME()整数值，整数不是您需要引用的表名等。

你应该只将整数转换为字符串，不带引号......

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],
    case when SUM(CASE WHEN LEN(' + QUOTENAME(@DATA_ATTRIBUTE) + N') >' + CAST(@LENGTHSIZE AS NVARCHAR(MAX)) + N' THEN 1 ELSE 0 END) > 0 then ''Y'' else ''N'' end as [Potential Issue(Y/N)]
    
FROM ' + QUOTENAME(@TABLE_NAME)
  • QUOTENAME(@LENGTHSIZE)=>CAST(@LENGTHSIZE AS NVARCHAR(MAX))

推荐阅读