本文介绍了CROSS APPLY 不适用于 SQL SERVER 2000?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在 SQL Server 2000 中使用等效于 CROSS APPLY 的东西?
How can I use something equivalent of CROSS APPLY in SQL Server 2000 ?
我有一个函数返回传递的 id 的顶级父级.
ALTER Function [dbo].[fn_GetTopParentRiskCategory]
(
@RctId int
)
RETURNS @TEMP_TABLE TABLE
(
rctId int,
topParentRiskCat Varchar(100)
)
AS
BEGIN
DECLARE @PARENTID INT
DECLARE @GRANDPARENTID INT
DECLARE @CODE VARCHAR(100)
DECLARE @DESC VARCHAR(100)
DECLARE @PARENTCODE VARCHAR(100)
SELECT @PARENTID= rctParentID from RiskCategory where rctid=@RctId
SELECT @GRANDPARENTID= rctParentId from RiskCategory where rctid= @PARENTID
IF @GRANDPARENTID IS NULL
BEGIN
SELECT @CODE='ALL'
INSERT INTO @TEMP_TABLE SELECT @Rctid,@CODE
END
ELSE
BEGIN
SELECT @CODE=''
SELECT @DESC=rctDescription from RiskCategory where rctid=@PARENTID
WHILE(@PARENTID IS NOT NULL)
BEGIN
SELECT @PARENTCODE=rctCode from RiskCategory WHERE rctid=@PARENTID
IF @PARENTCODE='All'
SET @PARENTCODE=''
SELECT @CODE=@PARENTCODE +'.'+ @CODE
SELECT @PARENTID =rctParentID from Riskcategory where rctid= @PARENTID
END
SELECT @CODE=Substring(@CODE+@DESC,2,len(@CODE+@DESC))
INSERT INTO @TEMP_TABLE SELECT @RctID,@CODE
END
RETURN
END
用于查询如下
INSERT INTO @TopRiskCat
SELECT R.rskid
,a.topParentRiskCat AS TopLevelRiskCat
FROM RISKS R
INNER JOIN RiskAnalysis RA
ON R.rskId = RA.ranRiskId
INNER JOIN RiskCategory RC
ON RA.ranRiskAnalId = RC.rctId
CROSS APPLY fn_GetTopParentRiskCategory(RC.rctid) as a
它在 2000 年不起作用.我明天发布.请帮忙!!
It does not work in 2000. I have release tomorrow . Please help !!
我会很感激
谢谢
推荐答案
由于您使用的函数始终只返回一个结果,因此您不需要交叉应用.应该可以将您的表值函数更改为标量值函数.
As the function you use always only returns one result, you don't need a cross apply. It should be possible to change your table valued function to a scalar valued function.
SQL 语句
INSERT INTO @TopRiskCat
SELECT R.rskid
, a.topParentRiskCat AS TopLevelRiskCat
, fn_GetTopParentRiskCategory(RC.rctID)
FROM RISKS R
INNER JOIN RiskAnalysis RA ON R.rskId = RA.ranRiskId
INNER JOIN RiskCategory RC ON RA.ranRiskAnalId = RC.rctId
标量值函数
ALTER Function [dbo].[fn_GetTopParentRiskCategory] (@RctId int)
RETURNS Varchar(100) AS
BEGIN
DECLARE @topParentRiskCat Varchar(100)
DECLARE @PARENTID INT
DECLARE @GRANDPARENTID INT
DECLARE @CODE VARCHAR(100)
DECLARE @DESC VARCHAR(100)
DECLARE @PARENTCODE VARCHAR(100)
SELECT @PARENTID= rctParentID from RiskCategory where rctid=@RctId
SELECT @GRANDPARENTID= rctParentId from RiskCategory where rctid= @PARENTID
IF @GRANDPARENTID IS NULL
BEGIN
SELECT @topParentRiskCat = 'ALL'
END
ELSE
BEGIN
SELECT @CODE=''
SELECT @DESC=rctDescription from RiskCategory where rctid=@PARENTID
WHILE(@PARENTID IS NOT NULL)
BEGIN
SELECT @PARENTCODE=rctCode from RiskCategory WHERE rctid=@PARENTID
IF @PARENTCODE='All'
SET @PARENTCODE=''
SELECT @CODE=@PARENTCODE +'.'+ @CODE
SELECT @PARENTID = rctParentID from Riskcategory where rctid= @PARENTID
END
SELECT @CODE=Substring(@CODE+@DESC,2,len(@CODE+@DESC))
SELECT @topParentRiskCat = @CODE
END
RETURN @topParentRiskCat
END
这篇关于CROSS APPLY 不适用于 SQL SERVER 2000?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!