Подтвердить что ты не робот

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
)

Если я определяю функцию, как вернуть ее в виде таблицы?

4b9b3361

ОТВЕТЫ

Ответ 1

Вы не можете получить доступ к временным таблицам из 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 меньше, поскольку переменные таблицы хранятся в памяти.

Ответ 2

Используйте это как шаблон

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.)

Ответ 3

Вам нужна специальная функция, известная как функция table value. Ниже представлен несколько затянутый пример, который строит измерение даты для хранилище данных. Обратите внимание на предложение 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

Ответ 4

Вам не нужно (не следует использовать) функцию, насколько я могу судить. Хранимая процедура вернет табличные данные из любых операторов SELECT, которые вы включите, которые возвращают табличные данные.

Сохраненная процедура не использует инструкции RETURN.

СОЗДАТЬ ПРОЦЕДУРУ AS

SELECT stuff INTO # temptbl1

.......

ВЫБОР столбцов FROM #temptbln