如何在接受表作为输入的sql中编写函数并将结果作为表返回？ [英] How to write function in sql which accept table as input and return result back as table?

问题描述

以下是我用来创建函数的代码，该函数接受表作为输入，并在函数返回结果后以表格格式执行后：

Here is code I used to create function which accept table as input and after operaton within function return result in table format as:

CREATE FUNCTION fn_CalculateListing(@Listing ListingColumn READONLY)
RETURNS TABLE 
AS
BEGIN
    DECLARE @result TABLE (ID INT, Name VARCHAR(50))

    INSERT INTO @result(ID,Name)
        SELECT ROWID, Name
        FROM @Listing

    RETURN @Listing
END

我使用了table-inline参数但出现错误：

I have make use of table-inline parameter but gives error as:

Msg 137, Level 15, State 2, Procedure fn_CalculateListing, Line 16
Must declare the scalar variable "@Listring".

Msg 102, Level 15, State 31, Procedure fn_CalculateListing, Line 29
Incorrect syntax near 'BEGIN'.`

如何使其正确？

How can I make it correct?

推荐答案

您的过程语法是正确的。它应该工作

但是，你传递的是不正确的变量



看错误信息 -

必须声明标量变量@Listring。



它说Listring的问题，而在你的程序中你使用了清单



有意义吗？

Your procedure syntax is correct. It should work
However, You are passing the incorrect variable

see the error message -
Must declare the scalar variable "@Listring".

It says issue with "Listring" whereas in your procedure you have used "Listing"

Make sense?

这篇关于如何在接受表作为输入的sql中编写函数并将结果作为表返回？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！