在用户定义函数中使用动态SQL返回字符串（不修改数据） [英] Using Dynamic SQL in User Defined Function to return string (not modify data)

问题描述

我们的文档存储应用程序为每个客户都拥有一个唯一的几乎完全相同的数据库，但是每个客户一个表 DocumentIndexes 是唯一的，可以有任何列数和类型数。

Our document storage application has a unique database for each of our clients which are almost identical to each other, but one table DocumentIndexes is unique for each client and can have any number of columns and types.

我正在尝试创建一个可以调用的泛型函数（在名为 MYAPP_MASTER 的主数据库中）只需传入数据库名称和文档ID值，然后从指定数据库的 DocumentIndexes 表中获取列名和值即可。因为必须输入数据库名称，所以必须动态生成选择SQL并调用 sp_executesql 。

I am trying to create a generic function (within our "master" database called MYAPP_MASTER) that I can call and simply pass in a database name and a document ID value and get back the column names and values from from the specified database's DocumentIndexes table. Because I have to pass in the database name, I have to generate the selection SQL dynamically and call sp_executesql.

我有以下代码，它轮询 INFORMATION_SCHEMA.COLUMNS 表来确定所需的列，并且工作正常在存储过程中，但是我讨厌必须在每个需要这些来检索这些动态列值的存储过程中复制所有这些代码。我宁愿有一个函数返回这些列的字符串值，而不管数据库如何，并且该函数在我们的 MYAPP_MASTER 数据库中存在一次。同样，此代码可以工作，但是SQL不允许我将其放入函数中。

I have the following code which polls the INFORMATION_SCHEMA.COLUMNS table to determine the columns needed and it works just fine in a stored procedure, but I hate having to copy all this code in every stored procedure that needs these to retrieve these dynamic column values. I would rather have one function that returns the string value of these columns regardless of database and have the function exist once in our MYAPP_MASTER database. Again, this code works, but SQL won't allow me to put it into a function. Is there anyway around this?

USE MYAPP_MASTER
GO
DECLARE @DatabaseName varchar(255)
DECLARE @DocumentId int
SET @DatabaseName = 'SAMPLE_CLIENT_DB'
SET @DocumentId = 1234
DECLARE @DynamicIndexes nvarchar(max)
DECLARE @DynamicIndexesParam nvarchar(max)
DECLARE @DynamicIndexesSql nvarchar(max)
SET @DynamicIndexesParam = '@Indexes varchar(max) OUTPUT'
SET @DynamicIndexesSql = 'SELECT @Indexes = COALESCE(@Indexes + ''+ '''', '', '''') + CAST(COLUMN_NAME as varchar(max)) + '': '''''' + '' + CASE WHEN DI.'' + COLUMN_NAME + '' IS NOT NULL THEN CAST(DI.'' + COLUMN_NAME + '' as varchar(max)) ELSE '''''''' END '' FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''DocumentIndexes'' AND COLUMN_NAME <> ''DocumentID''; '
EXEC sp_executesql @DynamicIndexesSql, @DynamicIndexesParam, @Indexes = @DynamicIndexes OUTPUT
SET @DynamicIndexes = '''' + @DynamicIndexes
DECLARE @SelectionSql nvarchar(max)
SET @SelectionSql = 'SELECT ' + @DynamicIndexes + ' as DocumentIndexes FROM ' + @DatabaseName + '..Document D LEFT OUTER JOIN ' + @DatabaseName + '..DocumentIndexes DI ON D.DocumentId = DI.DocumentId WHERE D.DocumentID = ' + CAST(@DocumentId as varchar(10))
EXEC sp_executesql @SelectionSql

如果 SAMPLE_CLIENT_DB 数据库 DocumentIndexes 表具有名称，办公室和分类列，此代码将返回一个简单的字符串，看起来像以下内容：

If the SAMPLE_CLIENT_DB datababase DocumentIndexes table has columns for Name, Office and Classification, this code will return a simple string that looks like the following:

Name: Foo, Office: Bar, Classification: 123

推荐答案

您无法在SQL函数中运行Exec命令，但可以将存储过程与输出配合使用假设DocumentIndex的变量es表在DocumentID级别是唯一的。

You can't run an Exec command inside a SQL function, but you could use a stored procedure with an output variable assuming the DocumentIndexes table is unique at the DocumentID level.

Create Proc DocID_DocIndexes @retval Varchar(Max) Output
As
...
(Your code logic minus last two lines)
...
--  Populate your dynamic SQL into a variable to assign it to the output variable
SET @SelectionSql = 'SELECT @result = ' + @DynamicIndexes + ' as DocumentIndexes FROM ' + @DatabaseName + '..Document D LEFT OUTER JOIN ' + @DatabaseName + '..DocumentIndexes DI ON D.DocumentId = DI.DocumentId WHERE D.DocumentID = ' + CAST(@DocumentId as varchar(10))
EXEC sp_executesql @SelectionSql, N'@result Varchar(Max) Output', @result = @retval Output
Return 

这篇关于在用户定义函数中使用动态SQL返回字符串（不修改数据）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！