这更像是一个语法问题,我正在尝试编写一个可以嵌入到查询中的存储过程或函数,例如:
select * from MyBigProcOrFunction
我正在尝试定义一个表格函数,但我不明白如何做到这一点,因为我构建tmp表来计算数据,然后我终于在endtable返回了.我的代码标记是:
create function FnGetCompanyIdWithCategories()
returns table
as
return
(
select * into a #tempTable from stuff
'
etc
'
select companyid,Company_MarketSector from #tempTables 'the returning table data
)
如果我定义一个函数,我该如何将它作为表格返回？
1> JoshBerke..：
您无法从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> TheTXI..：
将其用作模板
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION
(
-- Add the parameters for the function here
<@param1, sysname, @p1> ,
<@param2, sysname, @p2>
)
RETURNS
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
-- Add the column definitions for the TABLE variable here
,
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
RETURN
END
GO
这将定义您的功能.然后你就可以像任何其他表一样使用它:
Select * from MyFunction(Param1, Param2, etc.)
3> ConcernedOfT..：
您需要一种特殊类型的函数,称为表值函数. 下面是一个有点冗长的示例,它为数据仓库构建日期维度.请注意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
那个例子太长了
京公网安备 11010802040832号