如何在SQL 2000中实现OUTER APPLY和表值函数 [英] How to implement OUTER APPLY and table valued function in SQL 2000

问题描述

我在SQL 2005兼容级别90中创建了一个SQL存储过程。

但是当我部署相同的代码时我们在服务器上找到可达性等级为80.现在我们不能在Prod服务器上将可达性级别更改为90.



我使用下面的代码：

Hi,

I created a SQL store procedure in SQL 2005 compatibility level 90.
But when I deployed the same code we found on server comatibility level is 80. Now we cant change comtability level to 90 on Prod server.

I am using below code:

select Results.value from ##NameSplitedOnSpace as tbl outer apply dbo.fn_SplitString(tbl.value,'-') as Results

上面的代码在SQL Server 2005中运行良好，兼容性为90，但由于SQL 2000不支持Cross，因此不在80的可兼容性级别申请



请帮助我获取支持80级的上述代码。



感谢advan ce。

Above code works fine in SQL server 2005 with compatibility 90 but not in comatibility level 80 as SQL 2000 doesnt support Cross apply

Please help me to get the above code which support level 80.

Thanks in advance.

推荐答案

你好。在MS SQL 2000中无法使用非静态参数连接表值函数。

例如，带有静态字符串的代码将起作用

Hello. There is no way to join table valued functions with non-static argument in MS SQL 2000.
For instance the code with static string will work

SELECT Results.value
  FROM ##NameSplitedOnSpace AS tbl
  JOIN dbo.fn_SplitString('my_sample', '-') AS Results
    ON Results.MyColumn = tbl.MyOtherColumn

虽然具有指定表的列的代码不会。

考虑使用游标来实现此目的。这可能有更大的代码，但你会保持兼容性。

While the code with specified table's column will not.
Consider to use cursor for this purpose. This might have a bigger code but you'll maintain compatibility.

DECLARE @val AS varchar (255)
DECLARE @v_table TABLE ( /*here goes your ##NameSplitedOnSpace table structure or part of it */ )

DECLARE vals CURSOR FOR
 SELECT tbl.idx
   FROM ##NameSplitedOnSpace as tbl

 OPEN vals
FETCH NEXT FROM vals INTO @val
  
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO @v_table
  SELECT * --or use specific columns for correlation with @v_table table
    FROM dbo.fn_SplitString(@val, '-')
   FETCH NEXT FROM vals INTO @val
END

CLOSE vals
DEALLOCATE vals

SELECT * FROM @v_table

希望它会有所帮助。

祝你好运：）

Hope it would be helpful.
Good luck :)

这篇关于如何在SQL 2000中实现OUTER APPLY和表值函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！