sql-server - SQL Server 存储过程的限制 - 多个结果集/输出参数

Question

我无法找到 Azure 逻辑应用执行存储过程的限制。我发现不支持返回值，建议使用输出参数来缓解这种情况。

调用结果建议支持输出参数，但我无法让它工作:

存储过程:

CREATE PROCEDURE dbo.[TestOutputParameters]
    @paramIn AS INT,
    @errorCode INT = 0 OUTPUT,
    @errorMessage VARCHAR(255) = '' OUTPUT
AS 
BEGIN
    SELECT @errorCode = @paramIn, @errorMessage = 'Some message'
    SELECT 'testing' AS abc     
    SELECT 'testing2' AS cde

    RETURN @errorCode
END

在本地运行会产生预期的 2 个结果集:

1. 值为“testing”的 abc 列
2. 值为“testing2”的 cde 列

以及预期的输出参数@errorCode(值5)和@errorMessage(值“Some message”)

Azure 逻辑应用步骤:

"Execute_stored_procedure_(V2)": {
    "inputs": {
        "body": {
            "errorCode": "@variables('errorCode')",
            "errorMessage": "@variables('errorMessage')",
            "paramIn": 5
        },
        "host": {
            "connection": {
                "name": "@parameters('$connections')['sql']['connectionId']"
            }
        },
        "method": "post",
        "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/procedures/@{encodeURIComponent(encodeURIComponent('[dbo].[TestOutputParameters]'))}"
    },
    "runAfter": {
        "Initialize_variable_2": [
            "Succeeded"
        ]
    },
    "type": "ApiConnection"
}

输入主体是

"body": 
{
    "errorCode": 0,
    "errorMessage": "",
    "paramIn": 5
}

原始输出是:

"body": 
{
    "ResultSets": 
    {
        "Table1": 
        [
            {
                "abc": "testing"
            }
        ]
    },
    "OutputParameters": {}
}

在存储过程运行之前创建的变量@variables('errorCode')和@variables('errorMessage')保持不变，并且只有一个结果集返回。

这是 Azure 逻辑应用的限制吗？如果是这样，有推荐的解决方法吗？如果不是，我调用存储过程的错误在哪里？

Answer 1

如果您在本地使用，我确实同意@AlwaysLearning，如果不在本地使用，那么我已在我的环境中重现，以下是预期结果:

存储过程是:

CREATE or ALTER PROCEDURE dbo.[TestOutputParameters]
    @paramIn AS INT
    ,@errorCode INT = 0 OUTPUT
    ,@errorMessage AS VARCHAR(255) OUTPUT 
AS BEGIN
    SELECT @errorCode = @paramIn, @errorMessage = 'Some message'
    SELECT 'testing' AS abc     
    SELECT 'testing2' AS cde
    RETURN @errorCode
END

对我有用的设计:

解析 json 的架构是:

{
    "type": "object",
    "properties": {
        "body": {
            "type": "object",
            "properties": {
                "errorCode": {
                    "type": "integer"
                },
                "errorMessage": {
                    "type":[ "string","null"]
                },
                "paramIn": {
                    "type": "integer"
                }
            }
        }
    }
}

然后是 Sql 连接器:

输出:

代码 View :

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Execute_stored_procedure_(V2)": {
                "inputs": {
                    "body": {
                        "errorCode": "@body('Parse_JSON')?['body']?['errorCode']",
                        "errorMessage": "@body('Parse_JSON')?['body']?['errorMessage']",
                        "paramIn": "@body('Parse_JSON')?['body']?['paramIn']"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['sql']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/procedures/@{encodeURIComponent(encodeURIComponent('[dbo].[TestOutputParameters]'))}"
                },
                "runAfter": {
                    "Parse_JSON": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Initialize_variable": {
                "inputs": {
                    "variables": [
                        {
                            "name": "var",
                            "type": "object",
                            "value": {
                                "body": {
                                    "errorCode": 0,
                                    "errorMessage": "Rithwik",
                                    "paramIn": 8
                                }
                            }
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Parse_JSON": {
                "inputs": {
                    "content": "@variables('var')",
                    "schema": {
                        "properties": {
                            "body": {
                                "properties": {
                                    "errorCode": {
                                        "type": "integer"
                                    },
                                    "errorMessage": {
                                        "type": [
                                            "string",
                                            "null"
                                        ]
                                    },
                                    "paramIn": {
                                        "type": "integer"
                                    }
                                },
                                "type": "object"
                            }
                        },
                        "type": "object"
                    }
                },
                "runAfter": {
                    "Initialize_variable": [
                        "Succeeded"
                    ]
                },
                "type": "ParseJson"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {},
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "sql": {
                    "connectionId": "/subscriptions/b83c1ed3=/resourceGroups/rbojja/providers/Microsoft.Web/connections/sql",
                    "connectionName": "sql",
                    "id": "/subscriptions/b83c1ed3/providers/Microsoft.Web/locations/eastus/managedApis/sql"
                }
            }
        }
    }
}

尝试按照上述过程进行操作，您将得到与我一样的输出，但如果仍然无法得到，我建议您提出支持请求，因为我的流程中的所有内容都是正确的。