将表作为参数传递给 SQL Server UDF

Question

我想将一个表作为参数传递给标量 UDF。如果可能的话，我也希望将参数限制为仅具有一列的表。(可选)

这是否可能？

编辑： 我不想传递表名，我想传递数据表(我认为是作为引用)。

再次编辑： 我的标量 UDF 基本上需要接收一个值表，并返回行的 CSV 列表。

例如：

col1  
"My First Value"  
"My Second Value"
...
"My nth Value"

会返回

"My First Value, My Second Value,... My nth Value"

我想在表格上做一些过滤，例如确保没有空值并确保没有重复项。我期望的代码应该类似于：

SELECT dbo.MyFunction(SELECT DISTINCT myDate FROM myTable WHERE myDate IS NOT NULL)

- Nathan Koop

Answer 1

你可以使用用户定义的表类型，但不能使用任何表。根据文档：

对于Transact-SQL函数，所有数据类型均允许使用，包括CLR用户定义的类型和用户定义的表类型，但不包括时间戳数据类型。

你可以使用用户定义的表类型。

用户定义表类型的示例：

CREATE TYPE TableType 
AS TABLE (LocationName VARCHAR(50))
GO 

DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable

你可以定义一个表类型，例如TableType，并定义一个函数，该函数接受此类型的参数。以下是一个示例函数：

CREATE FUNCTION Example( @TableName TableType READONLY)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @name VARCHAR(50)

    SELECT TOP 1 @name = LocationName FROM @TableName
    RETURN @name
END

参数必须是只读的。以下是使用示例：

DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT * FROM @myTable

SELECT dbo.Example(@myTable)

根据您想要实现的目标，您可以修改这段代码。

编辑： 如果您有一个表格中的数据，则可以创建一个变量：

DECLARE @myTable TableType

从你的表中将数据取出并存到变量中

INSERT INTO @myTable(field_name)
SELECT field_name_2 FROM my_other_table

- Lukasz Lysik

---

啊，这正是我想要的，但似乎只适用于SqlServer 2008。我已经尝试在sqlserver 2005中使用，但出现语法错误，并且文档明确提到了2008。我查看了SqlServer 2005的Create Type文档，但选项中没有列出TABLE（http://msdn.microsoft.com/en-us/library/ms175007(SQL.90).aspx）。 - Nathan Koop

Answer 2

很遗憾，在SQL Server 2005中没有简单的方法。Lukasz'的回答对于SQL Server 2008是正确的，这个功能已经迟到了很久。

任何解决方案都需要使用临时表，或者传递xml/CSV并在UDF中解析。例如：转换为xml，在udf中解析。

DECLARE @psuedotable xml

SELECT
    @psuedotable = ...
FROM
    ...
FOR XML ...

SELECT ... dbo.MyUDF (@psuedotable)

不过，从更大的角度来看你想做什么？也许有另外一种方法可以实现...

编辑: 为什么不将查询作为字符串传递并使用带有输出参数的存储过程

注意：这是未经测试的代码片段，您需要考虑SQL注入等问题。但是，它也满足您的“一个列”的要求，应该会对您有所帮助

CREATE PROC dbo.ToCSV (
    @MyQuery varchar(2000),
    @CSVOut varchar(max)
)
AS
SET NOCOUNT ON

CREATE TABLE #foo (bar varchar(max))

INSERT #foo
EXEC (@MyQuery)

SELECT
    @CSVOut = SUBSTRING(buzz, 2, 2000000000)
FROM
    (
    SELECT 
        bar -- maybe CAST(bar AS varchar(max))??
    FROM 
        #foo
    FOR XML PATH (',')
    ) fizz(buzz)
GO

- gbn

Answer 3

步骤1：创建一个名为TableType的表类型，它将接受一个具有一个varchar列的表。

create type TableType
as table ([value] varchar(100) null)

步骤 2: 创建一个函数，该函数将接受上述声明的 TableType 作为表值参数和字符串值作为分隔符。

create function dbo.fn_get_string_with_delimeter (@table TableType readonly,@Separator varchar(5))
returns varchar(500)
As
begin

    declare @return varchar(500)

    set @return = stuff((select @Separator + value from @table for xml path('')),1,1,'')

    return @return

end

第三步：将只有一个varchar列的表以逗号为分隔符传递给用户定义的类型TableType，并在函数中使用。

select dbo.fn_get_string_with_delimeter(@tab, ',')

- Rahul Srivastava

---

太棒了，这正是我在寻找的。 - Muflix

Answer 4

简而言之，您想要像SELECT x FROM y这样的查询语句被传入一个函数，并将返回值作为逗号分隔的字符串。

正如已经解释过的那样，您可以通过创建表类型并将UDT传递到函数中来实现此目的，但这需要多行语句。

您可以在不声明类型化表的情况下传递XML，但似乎仍需要一个XML变量，这仍然是一个多行语句，即

DECLARE @MyXML XML = (SELECT x FROM y FOR XML RAW);
SELECT Dbo.CreateCSV(@MyXml);

"

“FOR XML RAW”让SQL将结果集作为某些xml输出。

但是您可以使用Cast(... AS XML)绕过变量。然后只需要进行一些XQuery和一些连接技巧：

"

CREATE FUNCTION CreateCSV (@MyXML XML) 
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @listStr VARCHAR(MAX);
    SELECT 
            @listStr = 
                COALESCE(@listStr+',' ,'') + 
                c.value('@Value[1]','nvarchar(max)') 
        FROM @myxml.nodes('/row') as T(c)
    RETURN @listStr
END
GO

-- And you call it like this:
SELECT Dbo.CreateCSV(CAST((    SELECT x FROM y    FOR XML RAW) AS XML));

-- Or a working example
SELECT Dbo.CreateCSV(CAST((
        SELECT DISTINCT number AS Value 
        FROM master..spt_values 
        WHERE type = 'P' 
            AND number <= 20
    FOR XML RAW) AS XML));

只要使用FOR XML RAW，您所需要做的就是将想要的列重命名为Value，因为该函数中已经硬编码了这个名称。

- Stephen Turner

Answer 5

将表作为参数传递给存储过程

步骤1：

创建表[DBO].T_EMPLOYEES_DETAILS ( Id int, Name nvarchar(50), Gender nvarchar(10), Salary int )

步骤2：

创建类型EmpInsertType作为表 ( Id int, Name nvarchar(50), Gender nvarchar(10), Salary int )

步骤3：

/* 必须在变量末尾添加READONLY关键字 */

创建PROC PRC_EmpInsertType @EmployeeInsertType EmpInsertType READONLY AS BEGIN INSERT INTO [DBO].T_EMPLOYEES_DETAILS SELECT * FROM @EmployeeInsertType END

步骤4：

DECLARE @EmployeeInsertType EmpInsertType

INSERT INTO @EmployeeInsertType VALUES(1,'John','Male',50000) INSERT INTO @EmployeeInsertType VALUES(2,'Praveen','Male',60000) INSERT INTO @EmployeeInsertType VALUES(3,'Chitra','Female',45000) INSERT INTO @EmployeeInsertType VALUES(4,'Mathy','Female',6600) INSERT INTO @EmployeeInsertType VALUES(5,'Sam','Male',50000)

EXEC PRC_EmpInsertType @EmployeeInsertType

=======================================

SELECT * FROM T_EMPLOYEES_DETAILS

输出

1 John 男 50000

2 Praveen 男 60000

3 Chitra 女 45000

4 Mathy 女 6600

5 Sam 男 50000

- Jamal Basha

Answer 6

我遇到了一个非常类似的问题，并且已经成功解决了，尽管我正在使用SQL Server 2000。我知道这是一个旧问题，但认为在此发布解决方案仍然有效，因为可能还有其他像我一样使用旧版本并需要帮助的人。

这里的诀窍是：SQL Server不接受将表传递给UDF，也不能传递T-SQL查询，以便函数创建临时表或甚至调用存储过程来执行此操作。所以，相反，我创建了一个保留表，我称之为xtList。这将保存要处理的值列表（1列，按需）。

CREATE TABLE [dbo].[xtList](
    [List] [varchar](1000) NULL
) ON [PRIMARY]

接下来是用于填充列表的存储过程。虽然这不是必需的，但我认为它非常有用且符合最佳实践。

-- =============================================
-- Author:      Zark Khullah
-- Create date: 20/06/2014
-- =============================================
CREATE PROCEDURE [dbo].[xpCreateList]
    @ListQuery varchar(2000)
AS
BEGIN
    SET NOCOUNT ON;

  DELETE FROM xtList

  INSERT INTO xtList
    EXEC(@ListQuery)
END

现在，您可以使用xtList以任何方式处理列表。您可以在过程中使用它（用于执行多个T-SQL命令），标量函数（用于检索多个字符串）或多语句表值函数（检索字符串，但像在表格中一样，每行1个字符串）。为了完成任何这些操作，您都需要使用游标：

DECLARE @Item varchar(100)
DECLARE cList CURSOR DYNAMIC
  FOR (SELECT * FROM xtList WHERE List is not NULL)
  OPEN cList

FETCH FIRST FROM cList INTO @Item
WHILE @@FETCH_STATUS = 0 BEGIN

  << desired action with values >>

FETCH NEXT FROM cList INTO @Item
END
CLOSE cList
DEALLOCATE cList

希望您能执行以下操作，具体取决于所创建的对象类型：

存储过程：

-- =============================================
-- Author:      Zark Khullah
-- Create date: 20/06/2014
-- =============================================
CREATE PROCEDURE [dbo].[xpProcreateExec]
(
    @Cmd varchar(8000),
    @ReplaceWith varchar(1000)
)
AS
BEGIN
  DECLARE @Query varchar(8000)

  << cursor start >>
    SET @Query = REPLACE(@Cmd,@ReplaceWith,@Item)
    EXEC(@Query)
  << cursor end >>
END

/* EXAMPLES

  (List A,B,C)

  Query = 'SELECT x FROM table'
    with EXEC xpProcreateExec(Query,'x') turns into
  SELECT A FROM table
  SELECT B FROM table
  SELECT C FROM table

  Cmd = 'EXEC procedure ''arg''' --whatchout for wrong quotes, since it executes as dynamic SQL
    with EXEC xpProcreateExec(Cmd,'arg') turns into
  EXEC procedure 'A'
  EXEC procedure 'B'
  EXEC procedure 'C'

*/

标量函数

-- =============================================
-- Author:      Zark Khullah
-- Create date: 20/06/2014
-- =============================================
CREATE FUNCTION [dbo].[xfProcreateStr]
(
    @OriginalText varchar(8000),
    @ReplaceWith varchar(1000)
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @Result varchar(8000)

  SET @Result = ''
  << cursor start >>
    SET @Result = @Result + REPLACE(@OriginalText,@ReplaceWith,@Item) + char(13) + char(10)
  << cursor end >>

    RETURN @Result
END

/* EXAMPLE

  (List A,B,C)

  Text = 'Access provided for user x'
    with "SELECT dbo.xfProcreateStr(Text,'x')" turns into
  'Access provided for user A
  Access provided for user B
  Access provided for user C'

*/

多语句表值函数

-- =============================================
-- Author:      Zark Khullah
-- Create date: 20/06/2014
-- =============================================
CREATE FUNCTION [dbo].[xfProcreateInRows]
(
    @OriginalText varchar(8000),
    @ReplaceWith varchar(1000)
)
RETURNS 
@Texts TABLE 
(
    Text varchar(2000)
)
AS
BEGIN
  << cursor start >>
      INSERT INTO @Texts VALUES(REPLACE(@OriginalText,@ReplaceWith,@Item))
  << cursor end >>
END

/* EXAMPLE

  (List A,B,C)

  Text = 'Access provided for user x'
    with "SELECT * FROM dbo.xfProcreateInRow(Text,'x')" returns rows
  'Access provided for user A'
  'Access provided for user B'
  'Access provided for user C'

*/

- Bernardo Dal Corno

Answer 7

要获取表格的列数，请使用以下代码：

select count(id) from syscolumns where id = object_id('tablename')

如果要将表格传递给函数，请尝试使用 XML，如此处所示here：

create function dbo.ReadXml (@xmlMatrix xml)
returns table
as
return
( select
t.value('./@Salary', 'integer') as Salary,
t.value('./@Age', 'integer') as Age
from @xmlMatrix.nodes('//row') x(t)
)
go

declare @source table
( Salary integer,
age tinyint
)
insert into @source
select 10000, 25 union all
select 15000, 27 union all
select 12000, 18 union all
select 15000, 36 union all
select 16000, 57 union all
select 17000, 44 union all
select 18000, 32 union all
select 19000, 56 union all
select 25000, 34 union all
select 7500, 29
--select * from @source

declare @functionArgument xml

select @functionArgument =
( select
Salary as [row/@Salary],
Age as [row/@Age]
from @source
for xml path('')
)
--select @functionArgument as [@functionArgument]

select * from readXml(@functionArgument)

/* -------- Sample Output: --------
Salary Age
----------- -----------
10000 25
15000 27
12000 18
15000 36
16000 57
17000 44
18000 32
19000 56
25000 34
7500 29
*/

- D3vtr0n

---

我不是在尝试获取表格列的计数。 - Nathan Koop

如果你试图限制它们只能使用一个列的表格，那么是的，你正在这样做。 - D3vtr0n

我正在尝试传递一个只包含一列的表，如果我传递一个有多列的表，我希望它抛出一个错误。就像如果我向期望浮点数的UDF传递了一个整数一样。 - Nathan Koop

那么您的意思是，如果想要这样做，您确实需要查询syscolumns表。否则你还能怎么做呢？ - D3vtr0n

Answer 8

    create table Project (ProjectId int, Description varchar(50));
    insert into Project values (1, 'Chase tail, change directions');
    insert into Project values (2, 'ping-pong ball in clothes dryer');

    create table ProjectResource (ProjectId int, ResourceId int, Name varchar(15));
    insert into ProjectResource values (1, 1, 'Adam');
    insert into ProjectResource values (1, 2, 'Kerry');
    insert into ProjectResource values (1, 3, 'Tom');
    insert into ProjectResource values (2, 4, 'David');
    insert into ProjectResource values (2, 5, 'Jeff');


    SELECT *, 
      (SELECT Name + ' ' AS [text()] 
       FROM ProjectResource pr 
       WHERE pr.ProjectId = p.ProjectId 
       FOR XML PATH ('')) 
    AS ResourceList 
    FROM Project p

-- ProjectId    Description                        ResourceList
-- 1            Chase tail, change directions      Adam Kerry Tom 
-- 2            ping-pong ball in clothes dryer    David Jeff 

- D. Kermott

Answer 9

以下代码将帮助您快速删除重复项、空值，并返回有效的列表。

CREATE TABLE DuplicateTable (Col1 INT)
INSERT INTO DuplicateTable
SELECT 8
UNION ALL
SELECT 1--duplicate
UNION ALL
SELECT 2 --duplicate
UNION ALL
SELECT 1
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION 
SELECT NULL
GO

WITH CTE (COl1,DuplicateCount)
AS
(
SELECT COl1,
ROW_NUMBER() OVER(PARTITION BY COl1 ORDER BY Col1) AS DuplicateCount
FROM DuplicateTable
WHERE (col1 IS NOT NULL) 
)
SELECT COl1
FROM CTE
WHERE DuplicateCount =1
GO

在SQL 2005中，CTE是有效的，您可以将值存储在临时表中并与您的函数一起使用。

- Raymond A

Answer 10

你可以像这样做：

/* 创建用户定义的表类型 */

CREATE TYPE StateMaster AS TABLE
(
 StateCode VARCHAR(2),
 StateDescp VARCHAR(250)
)
GO

/*创建一个以表格为参数的函数*/

CREATE FUNCTION TableValuedParameterExample(@TmpTable StateMaster READONLY)
RETURNS  VARCHAR(250)
AS
BEGIN
 DECLARE @StateDescp VARCHAR(250)
 SELECT @StateDescp = StateDescp FROM @TmpTable
 RETURN @StateDescp
END
GO

/*创建带有表作为参数的存储过程*/

CREATE PROCEDURE TableValuedParameterExample_SP
(
@TmpTable StateMaster READONLY
)
AS
BEGIN
 INSERT INTO StateMst 
  SELECT * FROM @TmpTable
END
GO


BEGIN
/* DECLARE VARIABLE OF TABLE USER DEFINED TYPE */
DECLARE @MyTable StateMaster

/* INSERT DATA INTO TABLE TYPE */
INSERT INTO @MyTable VALUES('11','AndhraPradesh')
INSERT INTO @MyTable VALUES('12','Assam')

/* EXECUTE STORED PROCEDURE */
EXEC TableValuedParameterExample_SP @MyTable
GO

更多详细信息请查看此链接：http://sailajareddy-technical.blogspot.in/2012/09/passing-table-valued-parameter-to.html

- Nayas Subramanian