将表作为参数传递给 sql server UDF

Question

提问by Nathan Koop

I'd like to pass a table as a parameter into a scaler UDF.

我想将表作为参数传递给定标器 UDF。

I'd also prefer to restrict the parameter to tables with only one column. (optional)

我还希望将参数限制为只有一列的表。（可选的）

Is this possible?

这可能吗？

EDIT

编辑

I don't want to pass a table name, I'd like to pass the table of data (as a reference I presume)

我不想传递表名，我想传递数据表（作为我假设的参考）

EDIT

编辑

I would want my Scaler UDF to basically take a table of values and return a CSV list of the rows.

我希望我的 Scaler UDF 基本上采用值表并返回行的 CSV 列表。

IE

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

would return

会回来

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

I'd like to do some filtering on the table though, IE ensuring that there are no nulls and to ensure there are no duplicates. I was expecting something along the lines of:

不过，我想对表进行一些过滤，IE 确保没有空值并确保没有重复项。我期待着以下内容：

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

Answer 1

采纳答案by gbn

Unfortunately, there is no simple way in SQL Server 2005. Lukasz' answer is correct for SQL Server 2008 though and the feature is longoverdue

不幸的是，在 SQL Server 2005 中没有简单的方法。Lukasz 的回答对 SQL Server 2008 是正确的，但该功能早就应该使用了

Any solution would involve temp tables, or passing in xml/CSV and parsing in the UDF. Example: change to xml, parse in udf

任何解决方案都将涉及临时表，或传入 xml/CSV 并在 UDF 中进行解析。例子：改成xml，在udf中解析

DECLARE @psuedotable xml

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

SELECT ... dbo.MyUDF (@psuedotable)

What do you want to do in the bigger picture though? There may be another way to do this...

但是，从更大的角度来看，您想做什么？可能还有另一种方法可以做到这一点......

Edit: Why not pass in the query as a string and use a stored proc with output parameter

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

Note: this is an untested bit of code, and you'd need to think about SQL injection etc. However, it also satisfies your "one column" requirement and should help you along

注意：这是一段未经测试的代码，您需要考虑 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

Answer 2

回答by Lukasz Lysik

You can, however no any table. From documentation:

您可以，但是没有任何表。从文档：

For Transact-SQL functions, all data types, including CLR user-defined types and user-defined table types, are allowed except the timestamp data type.

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

You can use user-defined table types.

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

Example of user-defined table type:

用户自定义表类型示例：

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

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

So what you can do is to define your table type, for example TableTypeand define funcion which takes the parameter of this type.An example function:

所以你可以做的是定义你的表类型，例如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

The parameter has to be READONLY. And example usage:

该参数必须是只读的。和示例用法：

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

SELECT dbo.Example(@myTable)

Depending of what you want achieve you can modify this code.

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

EDIT:If you have a data in a table you may create a variable:

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

DECLARE @myTable TableType

And take data from your table to the variable

并将数据从表中提取到变量

INSERT INTO @myTable(field_name)
SELECT field_name_2 FROm my_other_table

Answer 3

回答by Rahul Srivastava

Step 1: Create a Type as Table with name TableType that will accept a table having one varchar column

第 1 步：创建一个名为 TableType 的类型作为表，该类型将接受具有一个 varchar 列的表

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

Step 2: Create a function that will accept above declared TableType as Table-Valued Parameter and String Value as Separator

第 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

Step 3: Pass table with one varchar column to the user-defined type TableType and ',' as separator in the function

步骤 3：将带有一个 varchar 列的 table 传递给用户定义的类型 TableType 和 ',' 作为函数中的分隔符

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

Answer 4

回答by Stephen Turner

Cutting to the bottom line, you want a query like SELECT x FROM y to be passed into a function that returns the values as a comma separated string.

归根结底，您希望将诸如 SELECT x FROM y 之类的查询传递到一个函数中，该函数将值作为逗号分隔的字符串返回。

As has already been explained you can do this by creating a table type and passing a UDT into the function, but this needs a multi-line statement.

正如已经解释过的，您可以通过创建表类型并将 UDT 传递给函数来完成此操作，但这需要多行语句。

You can pass XML around without declaring a typed table, but this seems to need a xml variable which is still a multi-line statement i.e.

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

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

The "FOR XML RAW" makes the SQL give you it's result set as some xml.

“FOR XML RAW”使 SQL 将结果集作为一些 xml 提供给您。

But you can bypass the variable using Cast(... AS XML). Then it's just a matter of some XQuery and a little concatenation trick:

但是您可以使用 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));

As long as you use FOR XML RAW all you need do is alias the column you want as Value, as this is hard coded in the function.

只要您使用 FOR XML RAW，您所需要做的就是将您想要的列别名为 Value，因为这是在函数中硬编码的。

Answer 5

回答by Jamal

PASSING TABLE AS PARAMETER IN STORED PROCEDURE

传递表作为存储过程中的参数

Step 1:

第1步：

CREATE TABLE [DBO].T_EMPLOYEES_DETAILS ( Id int, Name nvarchar(50), Gender nvarchar(10), Salary int )

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

Step 2:

第2步：

CREATE TYPE EmpInsertType AS TABLE ( Id int, Name nvarchar(50), Gender nvarchar(10), Salary int )

CREATE TYPE EmpInsertType AS TABLE (Id int, Name nvarchar(50), Gender nvarchar(10), Salary int )

Step 3:

第 3 步：

/* Must add READONLY keyword at end of the variable */

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

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

Step 4:

第四步：

DECLARE @EmployeeInsertType EmpInsertType

声明 @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)

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

EXEC PRC_EmpInsertType @EmployeeInsertType

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

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

SELECT * FROM T_EMPLOYEES_DETAILS

OUTPUT

输出

1 John Male 50000

1 约翰男 50000

2 Praveen Male 60000

2 Praveen 男 60000

3 Chitra Female 45000

3 Chitra 女 45000

4 Mathy Female 6600

4 马蒂女 6600

5 Sam Male 50000

5 山姆男 50000

Answer 6

回答by Z. Khullah

I've been dealing with a very similar problem and have been able to achieve what I was looking for, even though I'm using SQL Server 2000. I know it is an old question, but think its valid to post here the solution since there should be others like me that use old versions and still need help.

我一直在处理一个非常相似的问题，并且能够实现我想要的，即使我使用的是 SQL Server 2000。我知道这是一个老问题，但认为在这里发布解决方案是有效的，因为应该还有像我这样使用旧版本但仍然需要帮助的人。

Here's the trick: SQL Server won't accept passing a table to a UDF, nor you can pass a T-SQL query so the function creates a temp table or even calls a stored procedure to do that. So, instead, I've created a reserved table, which I called xtList. This will hold the list of values (1 column, as needed) to work with.

诀窍如下：SQL Server 不接受将表传递给 UDF，也不能传递 T-SQL 查询，因此该函数会创建临时表，甚至调用存储过程来执行此操作。因此，相反，我创建了一个保留表，我称之为 xtList。这将保存要使用的值列表（1 列，根据需要）。

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

Then, a stored procedure to populate the list. This is not strictly necessary, but I think is very usefull and best practice.

然后，一个存储过程来填充列表。这不是绝对必要的，但我认为这是非常有用的最佳实践。

-- =============================================
-- 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

Now, just deal with the list in any way you want, using the xtList. You can use in a procedure (for executing several T-SQL commands), scalar functions (for retrieving several strings) or multi-statement table-valued functions (retrieves the strings but like it was inside a table, 1 string per row). For any of that, you'll need cursors:

现在，只需使用 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

The desired action would be as follows, depending on which type of object created:

所需的操作如下，具体取决于创建的对象类型：

Stored procedures

存储过程

-- =============================================
-- 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'

*/

Scalar functions

标量函数

-- =============================================
-- 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'

*/

Multi-statement table-valued functions

多语句表值函数

-- =============================================
-- 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'

*/

Answer 7

回答by D. Kermott

    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

Answer 8

回答by Raymond A

The following will enable you to quickly remove the duplicate,null values and return only the valid one as list.

以下将使您能够快速删除重复的空值并仅将有效值作为列表返回。

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

CTE are valid in SQL 2005 , you could then store the values in a temp table and use it with your function.

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

Answer 9

回答by D3vtr0n

To obtain the column count on a table, use this:

要获取表上的列数，请使用以下命令：

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

and to pass a table to a function, try XML as show here:

并表传递给一个函数，尝试XML作为显示在这里：

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
*/

Answer 10

回答by Nayas Subramanian

you can do something like this

你可以做这样的事情

/* CREATE USER DEFINED TABLE TYPE */

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

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

/*CREATE FUNCTION WHICH TAKES TABLE AS A PARAMETER */

/*创建以表为参数的函数*/

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 STORED PROCEDURE WHICH TAKES TABLE AS A PARAMETER */

/*创建以表为参数的存储过程*/

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

For more details check this link: http://sailajareddy-technical.blogspot.in/2012/09/passing-table-valued-parameter-to.html

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

将表作为参数传递给 sql server UDF

提问by Nathan Koop

采纳答案by gbn

回答by Lukasz Lysik

回答by Rahul Srivastava

回答by Stephen Turner

回答by Jamal

PASSING TABLE AS PARAMETER IN STORED PROCEDURE

传递表作为存储过程中的参数

回答by Z. Khullah

回答by D. Kermott

回答by Raymond A

回答by D3vtr0n

回答by Nayas Subramanian

相关推荐

最近更新

标签

将表作为参数传递给 sql server UDF

提问by Nathan Koop

采纳答案by gbn

回答by Lukasz Lysik

回答by Rahul Srivastava

回答by Stephen Turner

回答by Jamal

PASSING TABLE AS PARAMETER IN STORED PROCEDURE

传递表作为存储过程中的参数

回答by Z. Khullah

回答by D. Kermott

回答by Raymond A

回答by D3vtr0n

回答by Nayas Subramanian

相关推荐

如何计算两列（SQL）中具有相同值的行？

SQL 如何在sqlite3中输入类型转换

SQL 使用 Exists 1 或 Exists * 的子查询

SQL 如何比较Oracle中的日期？

相关推荐

最近更新

标签