Menu
What is a table-valued function in SQL Server. A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.
The RETURN exits the stored procedure, and nothing that follows it will be executed, including the SELECT statement on the following line. Otherwise, if you want the data for the entire table, as your question shows, add a SELECT after the INSERT . But don't put RETURN in front of it!
A Function procedure returns a value to the calling code either by executing a Return statement or by encountering an Exit Function or End Function statement.
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
Based on comments to this question here is my recommendation. You want to 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 probably performs a little bit better since you're not using the secondary temporary table (performance is a complete guess on my part).
If you're 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 you're indexing your temp table, there should be no issue, and you will be using the tempDb less since table variables are kept in memory.
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.)
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With