包含查询中的函数？

I have a query on a test database that relies on a function installed on that database. I need to run that query against another server that doesn't have that function and on which I can not install that function.

我对测试数据库有一个查询，它依赖于安装在该数据库上的函数。我需要针对没有该功能的另一台服务器运行该查询，并且我无法安装该功能。

Are there any elegant and/or practical solutions to this? The function is quite complicated with a number of parameters and so I had hoped there was a syntax that would allow me to label off a section of my query as a function and then call that code from the main body of the query.

这有什么优雅和/或实用的解决方案吗？这个函数非常复杂，有很多参数，所以我希望有一种语法允许我将查询的一部分标记为函数，然后从查询的主体中调用该代码。

I have tried to Google but unfortunately, as you might imagine, the words involved just lead to articles on how to create functions.

我试过谷歌但不幸的是，正如你可能想象的那样，所涉及的词语只会引发关于如何创建功能的文章。

Edit: I am limited to SQL Server 2000 functionality for this.

编辑：我仅限于SQL Server 2000功能。

3 个解决方案

#1

You can't just create an inline function? The create function fails as you don't have authority? You might be able to substitute a CTE.

你不能只创建一个内联函数？由于没有权限，create函数会失败？您可以替换CTE。

CREATE FUNCTION fn_Test ( @sID nvarchar(30) )
RETURNS table
AS
RETURN (
        SELECT sID, sParID 
        FROM docSVsys 
        WHERE sID = @sID
       )
GO
-- Example of calling the function for a specific region
SELECT *
FROM fn_Test(N'2')
GO

#2

My best answer at the moment came from myself. I stored the data I needed to process in a #temporary table and then used a while loop with the logic required updating rows back in the #temporary table. Quite a bit of reworking, but it worked and as a run-once operation I'm fine with that.

目前我最好的答案来自我自己。我在#temporary表中存储了我需要处理的数据，然后在#temporary表中使用了一个需要更新行的逻辑的while循环。相当多的工作，但它工作，作为一次运行操作，我很好。

#3

I do not know if this is possible in MSSQL 2000 but you might be able to create a function on tempdb and use it from there ..

我不知道这在MSSQL 2000中是否可行，但您可以在tempdb上创建一个函数并从那里使用它。

In 2008 it seems to work. So if you have rights to do that on tempdb this is likely to work.

在2008年它似乎工作。因此，如果您有权在tempdb上执行此操作，则可能会起作用。

#1