복붙노트

[SQL] SQL 서버에 매개 변수로 테이블을 통과 UDF

SQL

SQL 서버에 매개 변수로 테이블을 통과 UDF

나는 스케일러 UDF에 매개 변수로 테이블을 통과하고 싶습니다.

또한 하나 개의 컬럼과 테이블 매개 변수를 제한 할 원합니다. (선택 과목)

이것이 가능한가?

편집하다

나는 테이블 이름을 전달하고 싶지 않아, 나는 (내가 가정 기준으로) 데이터의 테이블을 통과하고 싶습니다

편집하다

내 스케일러 UDF는 기본적으로 값의 테이블을 가지고 행의 CSV 목록을 반환 할 것입니다.

IE

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

반환

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

나는 IE가 더 널 (null)이 없음을 보장하고 더 중복이 없는지 확인하기 위해,하지만 테이블에 몇 가지 필터링을하고 싶습니다. 나는의 라인을 따라 뭔가를 기다리고 있었다 :

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

해결법

  1. ==============================

    1.불행하게도,이 SQL 서버 2005 루카스 '대답 간단한 방법은 SQL Server 2008의 생각에 대해서 올바른 없으며이 기능은 늦은 감

    불행하게도,이 SQL 서버 2005 루카스 '대답 간단한 방법은 SQL Server 2008의 생각에 대해서 올바른 없으며이 기능은 늦은 감

    모든 솔루션은 임시 테이블을 포함, 또는 UDF에서 XML / CSV에 전달하고 분석하는 것이다. 예 : 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
  2. ==============================

    2.당신은, 그러나 모든 테이블 전혀 없이도 할 수 있습니다. 문서에서 :

    당신은, 그러나 모든 테이블 전혀 없이도 할 수 있습니다. 문서에서 :

    당신은 사용자 정의 테이블 형식을 사용할 수 있습니다.

    사용자 정의 테이블 형식의 예 :

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

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

    당신이 할 수있는 것은 예를 들어 TABLETYPE를 들어, 테이블 유형을 정의하고이 type.An 예 함수의 매개 변수를 funcion을 정의하는 것입니다 그래서 :

    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

    매개 변수는 READONLY 수 있습니다. 그리고 예를 들어, 사용 :

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

    3.1 단계 : 하나 개의 VARCHAR 컬럼을 갖는 테이블을 받아 이름 TABLETYPE 테이블과 같은 유형 만들기

    1 단계 : 하나 개의 VARCHAR 컬럼을 갖는 테이블을 받아 이름 TABLETYPE 테이블과 같은 유형 만들기

    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 : 사용자 정의 형식 TABLETYPE 및 하나 VARCHAR 컬럼 패스 테이블 ','기능 세퍼레이터로서

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

    4.하단 라인에 절단, 당신은 y는 쉼표로 반환 값이 문자열을 분리하는 함수로 전달 될 FROM SELECT 같은 쿼리는 X합니다.

    하단 라인에 절단, 당신은 y는 쉼표로 반환 값이 문자열을 분리하는 함수로 전달 될 FROM SELECT 같은 쿼리는 X합니다.

    이미 당신이 테이블 유형을 작성하고 기능에 UDT를 통과하여이 작업을 수행 할 수 있습니다 설명되었다, 그러나 이것은 여러 줄 문을해야합니다.

    당신은 유형이 지정된 테이블을 선언하지 않고 주위에 XML을 전달할 수 있지만, 여전히 여러 줄의 문장 즉 인 XML 변수를 필요로하는 것

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

    은 "FOR XML RAW"는 SQL 일부 XML로 그것의 결과 집합을 당신을 줄 수 있습니다.

    그러나 할 수 있습니다 (XML AS ...) 캐스트를 사용하여 변수 우회. 그런 다음 몇 가지 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));

    이 하드 기능에 코딩되어있는 한 당신은 XML RAW에 사용하는 모든 당신이 할 필요는 별칭 당신이 값으로 할 열입니다.

  5. ==============================

    5.나는 매우 비슷한 문제를 다루는 봤는데 내가이 오래된 질문 알고 내가 SQL 서버 2000을 사용하고 있더라도, 무엇을 찾고 있었다 달성하지만, 이후 여기에 솔루션을 게시 할 수의 유효 생각 할 수 있었다 이전 버전 여전히 필요가 도움을 사용하는 나 같은 사람이 있어야합니다.

    나는 매우 비슷한 문제를 다루는 봤는데 내가이 오래된 질문 알고 내가 SQL 서버 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'

*/
  6. ==============================

    6.1 단계:

    1 단계:

    [표 DBO] .T_EMPLOYEES_DETAILS을 만들 ( 아이디 INT, 이름 NVARCHAR (50) 성별 NVARCHAR (10) 급여 INT )

    2 단계:

    TYPE EmpInsertType AS 테이블을 생성 ( 아이디 INT, 이름 NVARCHAR (50) 성별 NVARCHAR (10) 급여 INT )

    3 단계 :

    / * 변수의 말에 READONLY 키워드를 추가해야합니다 * /

    PROC PRC_EmpInsertType을 CREATE @EmployeeInsertType EmpInsertType READONLY 같이 BEGIN     INSERT INTO [DBO] .T_EMPLOYEES_DETAILS     @EmployeeInsertType SELECT * FROM 종료

    4 단계 :

    @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

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

    T_EMPLOYEES_DETAILS SELECT * FROM

    산출

    요한 1 서 남성 50000

    2 프라 빈 남성 60000

    3 치트 여성 45000

    4 Mathy 여성 6600

    5 샘 퍼즐 50000

  7. ==============================

    7.테이블에 열 수를 구하려면, 이것을 사용 :

    테이블에 열 수를 구하려면, 이것을 사용 :

    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
*/
  8. ==============================

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

    9.다음은 신속하게 중복, 널 (null) 값을 제거하고 목록으로 만 유효 하나를 반환 할 수있게된다.

    다음은 신속하게 중복, 널 (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, 당신은 다음 임시 테이블에 값을 저장할 수 유효하며 함수와 함께 사용.

  10. ==============================

    10.당신은 이런 식으로 뭔가를 할 수

    당신은 이런 식으로 뭔가를 할 수

    / * 사용자 정의 테이블 TYPE을 생성 * /

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

    / * TABLE과 같은 매개 변수를 FUNCTION을 작성 * /

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

    / * TABLE과 같은 파라미터를 취하는 저장된 프로 시저를 만들 * /

    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

    11. from https://stackoverflow.com/questions/1609115/pass-table-as-parameter-into-sql-server-udf by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL ROWNUM 어떻게 특정 범위 사이에 행을 반환합니다  (0) 2020.04.01
[SQL] CodeIgniter의 액티브 레코드 패턴 UNION 쿼리  (0) 2020.04.01
[SQL] 목록 <>에 대한 SQL 저장 프로 시저를 전달  (0) 2020.04.01
[SQL] 어떻게 SSIS 패키지를 실행합니다 내 SQL Server 에이전트 작업에서 단계를 만들려면 어떻게해야합니까?  (0) 2020.04.01
[SQL] SQL에서 두 날짜 사이의 전체 개월 수를 계산  (0) 2020.04.01
,

티스토리툴바