tsql returning a table from a function or store procedure

Question

This is more of a syntax question I'm trying to write a store procedure or function that I can embed into a query such as:

select * from MyBigProcOrFunction

I'm trying to define a tabular function but i do not understand how to do it as I build tmp tables to work out the data before i finally have the return at the endtable. My mark up for my code is:

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

If i define a function, im not sure how to return it as a table, any help much appreciated!

Answer 1

You need a special type of function known as a table valued function. Below is a somewhat long-winded example that builds a date dimension for a data warehouse. Note the returns clause that defines a table structure. You can insert anything into the table variable (@DateHierarchy in this case) that you want, including building a temporary table and copying the contents into it.

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

Answer 2

Use this as a template

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

That will define your function. Then you would just use it as any other table:

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

Answer 3

You don't need (shouldn't use) a function as far as I can tell. The stored procedure will return tabular data from any SELECT statements you include that return tabular data.

A stored proc does not use RETURN statements.

CREATE PROCEDURE name AS

SELECT stuff INTO #temptbl1

.......

SELECT columns FROM #temptbln

Answer 4

You can't access Temporary Tables from within a SQL Function. You will need to use table variables so essentially:

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

Edit

Based on comments to this question here is my recommendation. You want join the results of either a procedure or table valued function in another query. I will show you how you can do it then you pick the one you prefer. I am going to be using sample code from one of my schemas, but you should be able to adapt it. Both are viable solutions first with a stored procedure.

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

In this case you have to declare a temporary table or table variable to store the results of the procedure. Now Let's look at how you would do this if you were using a UDF

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

As you can see the UDF is a lot more concise easier to read, and probally performs a little bit better since your not using the secondary temporary table (performance is a complete guess on my part).

If your going to be reusing your function/procedure in lots of other places, I think the UDF is your best choice. The only catch is you will have to stop using #Temp tables and use table variables. Unless your indexing your temp table there should be no issue, and you will be using the tempDb less since table variables are kept in memory.