테이블을 매개 변수로 sql 서버 UDF로 전달

테이블을 매개 변수로 sql 서버 UDF로 전달

테이블을 파라미터로 Scaler UDF에 전달하고 싶습니다.

또한 매개 변수를 열이 하나만 있는 테이블로 제한하는 것이 좋습니다.(선택 사항)

이것이 가능합니까?

편집

테이블 이름을 전달하는 것이 아니라 데이터 테이블을 전달하고 싶습니다(참고로).

편집

Scaler UDF에서 기본적으로 값 표를 작성하고 행의 CSV 목록을 반환해야 합니다.

IE

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

돌아올 것입니다

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

테이블에 대한 필터링을 수행하여 null이 없는지, 중복이 없는지 확인합니다.저는 다음과 같은 것을 기대하고 있었습니다.

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

할 수 있습니다, 아무리 자리도 없습니다.설명서에서:

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

안타깝게도 SQL Server 2005에는 간단한 방법이 없습니다.Lukasz의 답변은 SQL Server 2008에 대해 정확하며 이 기능은 오래 전에 사용되었습니다.

모든 솔루션에는 임시 테이블이 포함되거나, UDF에서 xml/CSV를 전달하고 구문 분석이 포함됩니다.예: xml로 변경, udf에서 구문 분석

DECLARE @psuedotable xml

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

SELECT ... dbo.MyUDF (@psuedotable)

하지만 당신은 더 큰 그림에서 무엇을 하고 싶습니까?다른 방법이 있을지도...

편집: 쿼리를 문자열로 전달하고 저장된 proc를 출력 매개 변수와 함께 사용하는 것은 어떻습니까?

참고: 이 코드는 테스트되지 않은 코드이며 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

1단계: 이름이 TableType인 Type을 사용하여 하나의 막대형 열이 있는 테이블을 허용하는 Type을 만듭니다.

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

2단계: 위에 선언된 TableType을 Table-Value Parameter로, 문자열 값을 구분 기호로 수락하는 함수를 만듭니다.

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단계: 하나의 막대 열이 있는 테이블을 사용자 정의 유형인 TableType 및 ', 함수의 구분 기호로 전달

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

결론적으로 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로 제공합니다.

그러나 캐스트(... 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를 사용하는 동안에는 원하는 열에 값이라는 별칭을 붙이기만 하면 됩니다. 이 열은 함수에서 하드 코딩됩니다.

저장 프로시저에서 테이블을 매개 변수로 전달

1단계:

테이블 [DBO]을(를)T_EMPIES_DETAILS (IDent, Name nvarchar(50)), 성별 nvarchar(10), 급여 int)

2단계:

CREATE TYPE EmpInsertType ASTABLE(ID, 이름 nvarchar(50), 성별 nvarchar(10), 급여 int)

3단계:

변수 끝에 READONLY 키워드를 추가해야 함 */

CREATE PROCPRC_EmpInsertType @직원[DBO]에 유형 EmpInsert유형 삽입 시작 시 읽기 전용입니다.T_Employee_DETALES 선택 * @Employee에서삽입 유형 END

4단계:

@직원 선언삽입 유형 Emp삽입 유형

@Employee에 삽입유형 값 삽입(1, 'John', 'Male', 50000) @Employee에 삽입유형 값 삽입(2, 'Praveen', 'Male', 60000) @Employee에 삽입유형 값 삽입(3, 'Chitra', '여성', 45000) @Employee에 삽입유형 값 삽입(4, 'Mathy', '여성', 6600) @Employee에 삽입삽입 유형 값(5, 'Sam', 'Male', 50000)

EXEC PRC_Emp직원 삽입 유형유형 삽입

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

선택 * T_EMPRESS_DETALES에서 선택

산출량

1 존 남성 50000

2 프라빈 남성 60000

3 치트라 여성 45000

4 Mathy 여성 6600

5 샘 남성 50000

저는 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 명령 실행용), 스칼라 함수(여러 문자열 검색용) 또는 다중 문 테이블 값 함수(테이블 내에 있는 것처럼 문자열 검색)에서 사용할 수 있습니다.이 경우 커서가 필요합니다.

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'

*/

표의 열 카운트를 가져오려면 다음을 사용합니다.

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

    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 

다음을 사용하면 중복된 null 값을 빠르게 제거하고 유효한 값만 목록으로 반환할 수 있습니다.

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에서 유효합니다. 그러면 값을 임시 테이블에 저장하고 기능과 함께 사용할 수 있습니다.

당신은 이런 것을 할 수 있습니다.

사용자 정의 테이블 유형 생성 */

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

언급URL : https://stackoverflow.com/questions/1609115/pass-table-as-parameter-into-sql-server-udf