创建内联表值函数sql-server 2008
|
我想使用以下语句创建内联表值函数
USE [abc]
GO
/****** Object: UserDefinedFunction [dbo].[fnspEmpPeriodSkus] Script Date: 04/14/2011 16:26:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnspEmpPeriodSkus]
(
-- Add the parameters for the function here
@PositionID int
)
RETURNS TABLE
AS
RETURN
(
declare @PeriodTypeID int
select @PeriodTypeID = cofig.PeriodTypeID from sysMarketSaleConfigurations config inner join setupJobs j on j.JobID = config.JobID
inner join setupDesignations d on d.JobID = j.JobID where d.DesignationID = @PositionID
SELECT EmployeeID,EmployeeName, ZoneID,Zone,RegionID,Region,TerritoryID,Territory, StartDate, EndDate, empperiods.PeriodID, empperiods.DesignationID, EmpSkus.SKUID, EmpSkus.SKUCode, EmpSkus.Title, EmpSkus.GroupName,
EmpSkus.ProductGroupID, EmpSkus.PositionID,EmpSkus.PositionTitle
FROM (SELECT StartDate, EndDate, PeriodID, DesignationID
FROM (SELECT sysPeriods.PeriodID, sysPeriods.StartDate, sysPeriods.EndDate, setupDesignations.DesignationID
FROM setupJobs INNER JOIN
setupDesignations ON setupJobs.JobID = setupDesignations.JobID CROSS JOIN
sysPeriods
WHERE Year(sysPeriods.StartDate) = Year(Current_timestamp) and (sysPeriods.PeriodTypeID = @PeriodTypeID)) AS drvtbl) AS empperiods LEFT OUTER JOIN
dbo.vwEmpSkus AS EmpSkus ON
empperiods.DesignationID = EmpSkus.PositionID
)
但它给我以下错误:关键字声明附近的语法不正确。
问题:是否可以在内联表值函数中做我想做的事情(声明一个变量并使用函数的参数从查询中获取结果)然后将该值传递给将计算结果的主查询)?如果是,怎么办?如果没有,则选择多语句表值函数是什么。我感谢建议和其他想法
没有找到相关结果
已邀请:
1 个回复
babsoft