主页 / dba / 问题 / 178200
Accepted
pmdci
Asked: 2017-07-07 14:39:45 +0800 CST

制作一个具有动态变量的TSQL语句

  • 772

我真的很难尝试将 TSQL 语句（它是一个存储过程）转换为动态 SQL。基本上我必须能够将架构名称作为参数传递。

这是我的代码：

DECLARE @query AS varchar(max)
DECLARE @schemaName AS varchar(5) = 'wl03' -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE) --The 11th month from the current month
DECLARE @plus12Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE) --The 12th month from the current month

DROP TABLE IF EXISTS #12thMonthExpectedActivity

-- Add rows for existing month into a temporary table
-- The code below returns an error: The data types varchar and date are incompatible in the add operator.
SET @query = 'SELECT ' + @plus12Month + 'AS period, genusId, subjectId, waitingStageId, value, ' + GETDATE() + ' AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName + '].[ExpectedActivity]
WHERE period in(' + @plus11Month + ')'
-- EXEC(@query) will go here once the above is working

/* This original code works just fine!
SELECT @plus12Month AS period, genusId, subjectId, waitingStageId, value, GETDATE() AS savedOn, '<Automated>' AS savedBy INTO #12thMonthExpectedActivity FROM [wl03].[ExpectedActivity]
  WHERE period in(@plus11Month)
*/

我遇到的问题是定义@query变量。我不断收到同样的错误：

消息 402，级别 16，状态 1，第 9 行数据类型 varchar 和 date 在 add 运算符中不兼容。

只要我不尝试将代码保存在查询中，该代码就可以正常工作。有任何想法吗？谢谢！

t-sql dynamic-sql

1 个回答

  1. Best Answer

    您的日期也必须是字符串，但格式要明确（例如日期格式 112）：

    DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE), 112) --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE), 112) --The 12th month from the current month

    您还需要转换您GETDATE()在字符串中的位置。注意引号，并转换回DATE。这是一个建议的解决方案：

    DECLARE @query AS VARCHAR(MAX);
DECLARE @schemaName AS VARCHAR(5) = 'wl03'; -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 11, 0) AS DATE), 112); --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 12, 0) AS DATE), 112); --The 12th month from the current month
DROP TABLE IF EXISTS #12thMonthExpectedActivity;

-- Add rows for existing month into a temporary table
SET @query
    = 'SELECT CAST(''' + @plus12Month + ''' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST('''
      + CONVERT(CHAR(8), GETDATE(), 112)
      + ''' AS DATE) AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName
      + '].[ExpectedActivity] WHERE period in (''' + @plus11Month + ''')';

PRINT @query;

    阅读有关联机丛书CAST的更多信息CONVERT。

    • 1

相关问题