問題描述

我有一個 UDF，它采用逗號分隔的列表并將其轉(zhuǎn)換為行所以

I have a UDF which takes a comma separated list and turns it into rows So the output of

select * from fnDrugSplit('one,two,three',',')

會

one
two
three

當我嘗試將這些結果插入到表變量中時

When I try to insert these results into a table variable with

declare @drugName1 table(drugName1 varchar(50),drugName2 varchar(50))
insert into @drugName1(drugName1,drugName2)
values(
         (select * from fnDrugSplit('one,two,three',',')
        ,(select * from fnDrugSplit('one,two,three',',')
      )

我發(fā)現(xiàn) ')' 附近的語法不正確，最后一個括號將 values 塊關閉.該函數(shù)將是確定性的，我不知道為什么會收到此錯誤，因為

I get Incorrect syntax near ')', the last parentheses closing out the values block. The function will is deterministic and I don't know why I'm getting this error because

declare @drugName1 table(drugName1 varchar(50),drugName2 varchar(50))
insert into @drugName1(drugName1,drugName2)
values(
         (select 'one')
        ,(select 'two')
      )
select * from @drugName1

工作正常.我在這里錯過了什么?

works fine. What am I missing here?

函數(shù)中的第二個參數(shù)是行的分隔符.SQL Server 2008

The second parameter in the function is the delimiter for rows. SQL Server 2008

推薦答案

您的 udf 返回一個包含 3 行的表.您不能使用VALUES"子句將 1 列的 3 行放入表中.VALUES"子句需要標量，這就是Select 'one'"和Select 'two'"起作用的原因.

Your udf returns a table with 3 rows. You can't put 3 rows of 1 column into the table with the "VALUES" clause. The "VALUES" clause expects scalars, which is why "Select 'one'" and "Select 'two'" work.

您不需要VALUES"，您可以明確表達您的選擇.

You don't need "VALUES" you can just articulate your select.

Insert into @drugName1
(drugName1, drugName2)
 select fn.ColName, fn.ColName
 from fnDrugSplit('one,two,three',',') fn

不確定如何將 3 個值放入 2 列，這在您的問題中不清楚.另外，我不知道您的 UDF 的列名是什么，所以我假設了 ColName.

Not sure how you want to put 3 values into 2 columns, that wasn't clear in your question. Also, I don't know what the column name is for your UDF, so I assumed ColName.

這篇關于使用來自 UDF 的結果集插入表變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！