主页 / dba / 问题 / 257262
Accepted
pmdci
Asked: 2020-01-14 03:49:21 +0800 CST

为列设置值的存储过程必须接受函数的返回值

  • 772

我在 Data Mart 中创建了一个非常简单的表来存储 ETL 诊断信息：

CREATE TABLE [internal].[etl_status]
(
    [property_name] VARCHAR(100) NOT NULL PRIMARY KEY, 
    [property_value] VARCHAR(MAX) NULL
)

然后我创建了一个存储过程来处理对该表的插入/修改。除了方便之外，我还必须这样做，因为 Microsoft 尚未将 T-SQL 语句活动添加到 Azure 数据工厂。

问题是，我不知道如何让存储过程接受 T-SQL 函数作为参数。事实上，我相信这可能是不可能的。所以这是我的小解决方法：

CREATE PROCEDURE [internal].[sp_set_etl_status]
    @propertyName VARCHAR(100)
   ,@propertyValue VARCHAR(MAX)
AS

DECLARE @query AS VARCHAR(MAX)

-- Case statement to handle functions passed as propertyValue parameter
-- NOT IDEAL, see solution by Scott Hodgin below
SET @propertyValue = CASE @propertyValue
    WHEN 'GETDATE()' THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME2(2), GETUTCDATE()))
    ELSE @propertyValue
    END

SET @query = '
BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] =''' + @propertyName + ''')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES (''' + @propertyName + ''',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = ''' + @propertyValue+ '''
WHERE [property_name] = ''' + @propertyName + '''
;'

EXEC(@query)

这按预期工作。但是对于当前的解决方案，我需要为我想要处理的每个函数都有一个 CASE 语句。

编辑： Scott Hodgin 下面的建议是一个可以接受的解决方案。但是我添加了一个默认值FALSEto@propertyValueIsFunction如下@propertyValueIsFunction BIT = FALSE：

stored-procedures azure-sql-database

1 个回答

  1. Best Answer

    如果可以向您的存储过程 ( PropertyValueIsFunction bit) 添加一个附加参数，您可以测试该条件并用于REPLACE去除引号。这是一个例子：

    CREATE PROCEDURE [dbo].[sp_set_etl_status]
    @propertyName VARCHAR(100)
   ,@propertyValue VARCHAR(MAX)
   ,@propertyValueIsFunction bit
AS

DECLARE @query AS VARCHAR(MAX)

SET @query = '
BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] =''' + @propertyName + ''')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES (''' + @propertyName + ''',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = ''' + @propertyValue+ '''
WHERE [property_name] = ''' + @propertyName + '''
;'
if @propertyValueIsFunction = 1
    set @query = replace(@query,'''' + @propertyValue + '''',@propertyValue) 
print @Query
--EXEC(@query)

    假设（注意额外的参数值 1 表示函数）：

    exec [dbo].[sp_set_etl_status] 'admission_last_import_start_dttm', 'GETDATE()',1

    生成的查询结果如下所示：

    BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] ='admission_last_import_start_dttm')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES ('admission_last_import_start_dttm',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = GETDATE()
WHERE [property_name] = 'admission_last_import_start_dttm'
;

    当您打印@query文本时，您会看到引号已从传入的函数中删除。

    您可能需要尝试不同的场景，但这是我解决原始问题的第一个想法。

    • 1

相关问题