tsql 함수 또는 저장 프로시저에서 표 반환

tsql 함수 또는 저장 프로시저에서 표 반환

이것은 다음과 같은 쿼리에 포함할 수 있는 스토어 프로시저 또는 함수를 작성하려는 구문 질문에 더 가깝습니다.

select * from MyBigProcOrFunction

표 함수를 정의하려고 하지만 최종 테이블에 반환하기 전에 데이터를 계산하기 위해 tmp 테이블을 구축하기 때문에 어떻게 해야 하는지 이해할 수 없습니다.내 코드에 대한 마크업은 다음과 같습니다.

create function FnGetCompanyIdWithCategories()
returns table
as 
return 
(
select * into a #tempTable from stuff
'
etc
'
select companyid,Company_MarketSector from #tempTables 'the returning table data
)

함수를 정의할 경우 테이블로 반환하려면 어떻게 해야 합니까?

SQL 함수 내에서 임시 테이블에 액세스할 수 없습니다.테이블 변수는 기본적으로 다음과 같이 사용해야 합니다.

ALTER FUNCTION FnGetCompanyIdWithCategories()
RETURNS  @rtnTable TABLE 
(
    -- columns returned by the function
    ID UNIQUEIDENTIFIER NOT NULL,
    Name nvarchar(255) NOT NULL
)
AS
BEGIN
DECLARE @TempTable table (id uniqueidentifier, name nvarchar(255)....)

insert into @myTable 
select from your stuff

--This select returns data
insert into @rtnTable
SELECT ID, name FROM @mytable 
return
END

편집

이 질문에 대한 의견을 바탕으로 다음과 같이 제안합니다.프로시저 또는 테이블 값 함수의 결과를 다른 쿼리에 결합하려고 합니다.제가 어떻게 하는지 알려드릴 테니 원하시는 것을 고르세요.저는 제 스키마 중 하나에서 샘플 코드를 사용할 예정이지만, 당신은 그것을 수정할 수 있을 것입니다.둘 다 저장 프로시저를 통해 먼저 실행 가능한 솔루션입니다.

declare @table as table (id int, name nvarchar(50),templateid int,account nvarchar(50))

insert into @table
execute industry_getall

select * 
from @table 
inner join [user] 
    on account=[user].loginname

이 경우 절차 결과를 저장할 임시 테이블 또는 테이블 변수를 선언해야 합니다.이제 UDF를 사용하는 경우 이 작업을 수행하는 방법을 살펴보겠습니다.

select *
from fn_Industry_GetAll()
inner join [user] 
    on account=[user].loginname

보시다시피 UDF는 훨씬 더 읽기 쉽고, 보조 임시 테이블을 사용하지 않기 때문에 성능이 조금 더 나을 수 있습니다(성능은 제가 완전히 추측하는 것입니다).

다른 많은 곳에서 기능/절차를 재사용할 경우 UDF가 최선의 선택이라고 생각합니다.#Temp 테이블 사용을 중지하고 테이블 변수를 사용해야 합니다.임시 테이블을 인덱싱하는 경우가 아니라면 문제가 없을 것이며 테이블 변수가 메모리에 저장되므로 tempDb를 덜 사용하게 될 것입니다.

템플릿으로 사용

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    RETURN 
END
GO

그것이 당신의 기능을 정의할 것입니다.그런 다음 다른 테이블로 사용할 수 있습니다.

Select * from MyFunction(Param1, Param2, etc.)

표 값 함수라고 하는 특수한 유형의 함수가 필요합니다.다음은 데이터 웨어하우스의 날짜 차원을 작성하는 다소 장황한 예입니다.참고:returns테이블 구조를 정의하는 절입니다.테이블 변수에 아무 것이나 삽입할 수 있습니다(@DateHierarchy임시 테이블을 만들고 테이블에 내용을 복사하는 작업을 포함하여 원하는 작업을 수행할 수 있습니다.

if object_id ('ods.uf_DateHierarchy') is not null
    drop function ods.uf_DateHierarchy
go

create function ods.uf_DateHierarchy (
       @DateFrom datetime
      ,@DateTo   datetime
) returns @DateHierarchy table (
        DateKey           datetime
       ,DisplayDate       varchar (20)
       ,SemanticDate      datetime
       ,MonthKey          int     
       ,DisplayMonth      varchar (10)
       ,FirstDayOfMonth   datetime
       ,QuarterKey        int
       ,DisplayQuarter    varchar (10)
       ,FirstDayOfQuarter datetime
       ,YearKey           int
       ,DisplayYear       varchar (10)
       ,FirstDayOfYear    datetime
) as begin
    declare @year            int
           ,@quarter         int
           ,@month           int
           ,@day             int
           ,@m1ofqtr         int
           ,@DisplayDate     varchar (20)
           ,@DisplayQuarter  varchar (10)
           ,@DisplayMonth    varchar (10)
           ,@DisplayYear     varchar (10)
           ,@today           datetime
           ,@MonthKey        int
           ,@QuarterKey      int
           ,@YearKey         int
           ,@SemanticDate    datetime
           ,@FirstOfMonth    datetime
           ,@FirstOfQuarter  datetime
           ,@FirstOfYear     datetime
           ,@MStr            varchar (2)
           ,@QStr            varchar (2)
           ,@Ystr            varchar (4)
           ,@DStr            varchar (2)
           ,@DateStr         varchar (10)


    -- === Previous ===================================================
    -- Special placeholder date of 1/1/1800 used to denote 'previous'
    -- so that naive date calculations sort and compare in a sensible
    -- order.
    --
    insert @DateHierarchy (
         DateKey
        ,DisplayDate
        ,SemanticDate
        ,MonthKey
        ,DisplayMonth
        ,FirstDayOfMonth
        ,QuarterKey
        ,DisplayQuarter
        ,FirstDayOfQuarter
        ,YearKey
        ,DisplayYear
        ,FirstDayOfYear
    ) values (
         '1800-01-01'
        ,'Previous'
        ,'1800-01-01'
        ,180001
        ,'Prev'
        ,'1800-01-01'
        ,18001
        ,'Prev'
        ,'1800-01-01'
        ,1800
        ,'Prev'
        ,'1800-01-01'
    )

    -- === Calendar Dates =============================================
    -- These are generated from the date range specified in the input
    -- parameters.
    --
    set @today = @Datefrom
    while @today <= @DateTo begin

        set @year = datepart (yyyy, @today)
        set @month = datepart (mm, @today)
        set @day = datepart (dd, @today)
        set @quarter = case when @month in (1,2,3) then 1
                            when @month in (4,5,6) then 2
                            when @month in (7,8,9) then 3
                            when @month in (10,11,12) then 4
                        end
        set @m1ofqtr = @quarter * 3 - 2 

        set @DisplayDate = left (convert (varchar, @today, 113), 11)
        set @SemanticDate = @today
        set @MonthKey = @year * 100 + @month
        set @DisplayMonth = substring (convert (varchar, @today, 113), 4, 8)
        set @Mstr = right ('0' + convert (varchar, @month), 2)
        set @Dstr = right ('0' + convert (varchar, @day), 2)
        set @Ystr = convert (varchar, @year)
        set @DateStr = @Ystr + '-' + @Mstr + '-01'
        set @FirstOfMonth = convert (datetime, @DateStr, 120)
        set @QuarterKey = @year * 10 + @quarter
        set @DisplayQuarter = 'Q' + convert (varchar, @quarter) + ' ' +
                                    convert (varchar, @year)
        set @QStr = right ('0' + convert (varchar, @m1ofqtr), 2)   
        set @DateStr = @Ystr + '-' + @Qstr + '-01' 
        set @FirstOfQuarter = convert (datetime, @DateStr, 120)
        set @YearKey = @year
        set @DisplayYear = convert (varchar, @year)
        set @DateStr = @Ystr + '-01-01'
        set @FirstOfYear = convert (datetime, @DateStr)


        insert @DateHierarchy (
             DateKey
            ,DisplayDate
            ,SemanticDate
            ,MonthKey
            ,DisplayMonth
            ,FirstDayOfMonth
            ,QuarterKey
            ,DisplayQuarter
            ,FirstDayOfQuarter
            ,YearKey
            ,DisplayYear
            ,FirstDayOfYear
        ) values (
             @today
            ,@DisplayDate
            ,@SemanticDate
            ,@Monthkey
            ,@DisplayMonth
            ,@FirstOfMonth
            ,@QuarterKey
            ,@DisplayQuarter
            ,@FirstOfQuarter
            ,@YearKey
            ,@DisplayYear
            ,@FirstOfYear
        )

        set @today = dateadd (dd, 1, @today)
    end

    -- === Specials ===================================================
    -- 'Ongoing', 'Error' and 'Not Recorded' set two years apart to
    -- avoid accidental collisions on 'Next Year' calculations.
    --
    insert @DateHierarchy (
         DateKey
        ,DisplayDate
        ,SemanticDate
        ,MonthKey
        ,DisplayMonth
        ,FirstDayOfMonth
        ,QuarterKey
        ,DisplayQuarter
        ,FirstDayOfQuarter
        ,YearKey
        ,DisplayYear
        ,FirstDayOfYear
    ) values (
         '9000-01-01'
        ,'Ongoing'
        ,'9000-01-01'
        ,900001
        ,'Ong.'
        ,'9000-01-01'
        ,90001
        ,'Ong.'
        ,'9000-01-01'
        ,9000
        ,'Ong.'
        ,'9000-01-01'
    )

    insert @DateHierarchy (
         DateKey
        ,DisplayDate
        ,SemanticDate
        ,MonthKey
        ,DisplayMonth
        ,FirstDayOfMonth
        ,QuarterKey
        ,DisplayQuarter
        ,FirstDayOfQuarter
        ,YearKey
        ,DisplayYear
        ,FirstDayOfYear
    ) values (
         '9100-01-01'
        ,'Error'
        ,null
        ,910001
        ,'Error'
        ,null
        ,91001
        ,'Error'
        ,null
        ,9100
        ,'Err'
        ,null
    )

    insert @DateHierarchy (
         DateKey
        ,DisplayDate
        ,SemanticDate
        ,MonthKey
        ,DisplayMonth
        ,FirstDayOfMonth
        ,QuarterKey
        ,DisplayQuarter
        ,FirstDayOfQuarter
        ,YearKey
        ,DisplayYear
        ,FirstDayOfYear
    ) values (
         '9200-01-01'
        ,'Not Recorded'
        ,null
        ,920001
        ,'N/R'
        ,null
        ,92001
        ,'N/R'
        ,null
        ,9200
        ,'N/R'
        ,null
    )

    return
end

go

제가 아는 한 기능은 필요하지 않습니다.저장 프로시저는 표 데이터를 반환하는 SELECT 문에서 표 데이터를 반환합니다.

저장된 프로시저는 RETURN 문을 사용하지 않습니다.

 CREATE PROCEDURE name
 AS

 SELECT stuff INTO #temptbl1

 .......


 SELECT columns FROM #temptbln

언급URL : https://stackoverflow.com/questions/440308/tsql-returning-a-table-from-a-function-or-store-procedure