通过表作为参数到SQL Server UDF(Pass table as parameter int

2019-08-31 11:50发布

站内文章 / 前端开发
26 0 0

我想传递一个表作为参数传入定标器UDF。

我也宁愿只有一个列限制参数表。 （可选的）

这可能吗？

编辑

我并不想传递一个表名，我想传递数据的表（作为我相信参考）

编辑

我希望我的倍线器UDF基本上可取的值的表并返回行的CSV列表。

IE 

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

将返回

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

我想这样做在桌子上一些过滤不过，IE确保有没有空，并确保有没有重复。 我期待的线沿线的东西： 

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

Answer 1:

不幸的是，在SQL Server中没有简单的方法，2005年卢卡斯的回答是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


Answer 2:

你可以，但是没有任何表。 从技术文档：

对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并定义funcion这需要这个type.An例如函数的参数： 

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


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

步骤3：在功能表传递与一个varchar列到用户定义的类型和TABLETYPE“”作为分隔符

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


Answer 4:

切底线，你要像选择x从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，所有你需要做的是别名，你想要的值列，因为这是很难在功能编码。



Answer 5:

我一直在处理一个非常类似的问题，已经能够达到什么样的我一直在寻找，即使我使用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'

*/


Answer 6:

为了获得在表中的列数，使用： 

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

并表传递给一个函数，尝试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 7:

    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:

下面将让您快速删除重复的，空值和只返回一个有效的列表。 

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是SQL 2005中有效的，然后你可以在值存储在一个临时表，并与您的函数中使用它。



Answer 9:

逝水表为参数存储过程

步骤1：

CREATE TABLE [DBO] .T_EMPLOYEES_DETAILS（ID INT，名称为nvarchar（50），性别为nvarchar（10），工资INT）

第2步：

CREATE TYPE EmpInsertType如表（ID INT，名称为nvarchar（50），性别为nvarchar（10），工资INT）

第3步：

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

CREATE 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， '约翰'， '男性'，50000）INSERT INTO @EmployeeInsertType VALUES（2， '普利文'， '男性'，60000）INSERT INTO @EmployeeInsertType VALUES（3， '奇特拉'，'女”，45000）INSERT INTO @EmployeeInsertType VALUES（4， 'Mathy'， '女'，6600）INSERT INTO @EmployeeInsertType VALUES（5， '萨姆'， '男性'，50000）

EXEC PRC_EmpInsertType @EmployeeInsertType

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

SELECT * FROM T_EMPLOYEES_DETAILS

OUTPUT

约翰一男50000

2普利文男60000

3奇特拉女45000

4 Mathy女6600

5山姆益智50000



Answer 10:

你可以做这样的事情

/ * CREATE USER定义的表类型* / 

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



文章来源: Pass table as parameter into sql server UDF
标签： sql sql-server sql-server-2005 parameters user-defined-functions
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~