将动态表名称传递给 SQL 函数并返回逗号分隔的字符串

Question

mytable 结构：id int，lookuptablename varchar

1, 'lookuptable1'
2, 'lookuptable2'

lookuptable1：id int，item varchar

1, 'item1 from lkt1'
2, 'item2 from lkt1'

lookuptable2：id int，item varchar

1, 'item1 from lkt2'
2, 'item2 from lkt2'

查询：

SELECT GetDelimitedList(lookuptablename) FROM mytable;

预期结果：

1,2~item1 from lkt1,item2 from lkt1
1,2~item1 from lkt2,item2 from lkt2

我有一直在努力寻找一种方法来以各种方式实现这一目标，但就是无法弄清楚。

Answer 1

一个解决方法。 UDF 将不起作用。也许是 CLR，但不是本机 SQL UDF。

首先创建此过程，它将表混合成一行

create proc dbo.GetDelimitedList
@tablename sysname
AS
declare @sql nvarchar(max)
set @sql = '
declare @id nvarchar(max), @item nvarchar(max)
select
    @id = isnull(@id+'','','''') + convert(varchar,id),
    @item = isnull(@item+'','','''') + item
from ' + quotename(@tablename) + '
select @id + ''~'' + @item'
exec (@sql)
GO

然后使用此 SQL 批处理生成相当于 SELECT GetDelimitedList(tablename) FROM mytable;

declare @tmp table(id int, mashed nvarchar(max))
declare @id int, @tablename sysname
-- start at first table
select top 1 @id = id, @tablename = tablename
from mytable
order by id asc
while @@ROWCOUNT > 0
begin
    -- fill our temp table
    insert @tmp (mashed) exec GetDelimitedList 'mytablelist'
    update @tmp set id = @id where id is null
    -- next table
    select top 1 @id=id, @tablename = tablename
    from mytable
    where id > @id
    order by id asc
end
SELECT * FROM @tmp;

使用的示例表：

create table mytable(id int, tablename varchar(100))
insert mytable values (1, 'mytablelist')
insert mytable values (3, 'mytablelist2')

create table mytablelist(id int, item varchar(100))
insert mytablelist values
(1, 'item1'),
(2, 'item2')
GO

create table mytablelist2(id int, item varchar(100))
insert mytablelist2 values
(11, 't2item1'),
(22, 't2item2')
GO

Answer 2

为了后代的利益，动态 Sql 通常应该是可参数化的（尽管这里没有必要）并通过 sp_executesql 执行。有关 EXEC 与 sp_executesql 的完整详细信息可以在这篇（优秀）文章中找到：

http://www.sommarskog。 se/dynamic_sql.html

将 Erland 的错误处理建议与编写动态存储过程的建议相结合，生成类似于以下内容的模板：

CREATE PROCEDURE [dbo].[prc_<>]
(
    @isDebug BIT = 0
)
AS
BEGIN TRY

    SET NOCOUNT ON 
    SET XACT_ABORT ON 

    DECLARE
        @nvQry NVARCHAR(MAX)        = NULL,
        @nvParams NVARCHAR(MAX) = NULL,
        @n NVARCHAR(MAX)                = NCHAR(13) + NCHAR(10)

    -- %% Setup query here %% --

    IF @isDebug = 1
    BEGIN
        PRINT N'DECLARE ' + @n + @nvParams + @n + @n
        PRINT @nvQry
    END
    ELSE
    BEGIN
        EXEC sp_executesql @nvQry, @nvParams, 
    END 

    RETURN 0 --No Error

END TRY
BEGIN CATCH
    -- Indicate that we want to rollback entire transaction stack, 
    --  (all BEGIN TRANSACTION calls increment @@TRANCOUNT and ROLLBACK
    --      TRANSACTION returns @@TRANCOUNT to zero)
    IF @@TRANCOUNT > 0
    BEGIN
        -- Justified here: http://www.sommarskog.se/error-handling-II.html#rollbackornot
        ROLLBACK TRANSACTION
    END

    -- Augement error message and re-raise
  EXECUTE [dbo].[prc_ErrorHandler]

    -- In case this is only a statement-termination
    RETURN -1 -- Arbitrary error return value

END CATCH
GO

在问题的情况下并以 @RichardTheKiwi 的答案为基础，我们最终得到公认的功能与上面的过程等效的过程，但更容易调试：

CREATE PROCEDURE [dbo].[GetDelimitedList]
(
    @tablename NVARCHAR(128),
    @isDebug BIT = 0
)
AS
BEGIN TRY

    SET NOCOUNT ON 
    SET XACT_ABORT ON 

    DECLARE
        @nvQry NVARCHAR(MAX)        = NULL,
        @nvParams NVARCHAR(MAX) = NULL,
        @n NVARCHAR(MAX)                = NCHAR(13) + NCHAR(10)

    -- %% Setup query here %% --
    SELECT
         @nvQry = 
            N'DECLARE ' + @n +
            N'  @id NVARCHAR(MAX), ' + @n +
            N'  @item NVARCHAR(MAX) ' + @n +
            N' ' + @n +
            N'SELECT ' + @n +
            N'  @id = ISNULL(@id + '','', '''') + CONVERT(VARCHAR, [id]), ' + @n +
            N'  @item = ISNULL(@item + '','', '''') + [item] ' + @n +
            N'FROM ' + QUOTENAME(@tablename) + @n +
            N' ' + @n +
            N'SELECT @id + ''~'' + @item'

    IF @isDebug = 1
    BEGIN
        PRINT @nvQry
    END
    ELSE
    BEGIN
        EXEC sp_executesql @nvQry
    END 

    RETURN 0 --No Error

END TRY
BEGIN CATCH
    -- Indicate that we want to rollback entire transaction stack, 
    --  (all BEGIN TRANSACTION calls increment @@TRANCOUNT and ROLLBACK
    --      TRANSACTION returns @@TRANCOUNT to zero)
    IF @@TRANCOUNT > 0
    BEGIN
        -- Justified here: http://www.sommarskog.se/error-handling-II.html#rollbackornot
        ROLLBACK TRANSACTION
    END

    -- Augement error message and re-raise
  EXECUTE [dbo].[prc_ErrorHandler]

    -- In case this is only a statement-termination
    RETURN -1 -- Arbitrary error return value

END CATCH
GO