在选择查询中使用存储过程

I have a stored procedure that I want to use in a SELECT like below but I get an error.

我有一个存储过程，我想在下面的SELECT中使用但我收到一个错误。

The stored procedure's output is a table with one column of type int

存储过程的输出是一个包含int类型列的表

select * from users where resCode in (EXEC getUserResselers 1)

-- stored procedure
create procedure getUserResselers
    @userCode int
as
    ;WITH Directories AS (SELECT  code FROM Resseler WHERE code =(select resselerCode from Users where code=@userCode)
    UNION ALL SELECT d.code FROM Resseler d INNER JOIN Directories p ON  d.parent = p.code)
        SELECT  * FROM    Directories d

1 个解决方案

#1

Direct selection from a stored procedure is not supported. However, this type of operation is a good candidate for a table-valued user-defined function, which might look something like:

不支持从存储过程直接选择。但是，这种类型的操作是表值用户定义函数的良好候选者，它可能看起来像：

^{Note that you have misspelled "resellers". I've left the misspelling so that you could test with your existing schema.}

请注意，您有拼写错误的“经销商”。我已经离开拼写错误，以便您可以使用现有架构进行测试。

CREATE FUNCTION getUserResselers
(
    @UserCode INT
)
RETURNS TABLE AS
RETURN(
    WITH Directories AS (

       SELECT  code FROM Reseller WHERE code = (select resselerCode from Users where code=@userCode)
       UNION ALL SELECT d.code FROM Resseler d INNER JOIN Directories p ON  d.parent = p.code
    )
    SELECT  * FROM   Directories
)

You could then include a join to the function in your query, or you could continue to use IN, which should be something like:

然后，您可以在查询中包含函数的连接，或者您可以继续使用IN，它应该类似于：

select * from users where resCode in (SELECT Code FROM getUserResselers(1));

#1