从另一个存储过程调用存储过程并将结果作为新列返回

How to call stored procedure from another stored procedure and return the result as first one's column?

如何从另一个存储过程调用存储过程并将结果作为第一列返回？

ALTER PROCEDURE [dbo].[GetItems]
AS
SET NOCOUNT ON

SELECT ID, AddedDate, Title,Description,
       Result of Stored Procedure "CountAll" call with parameter ID as Total 
FROM dbo.Table1

And also: how to be if CountAll stored procedure returns some columns and not just scalar?

而且：如果CountAll存储过程返回一些列而不仅仅是标量，该怎么办？

3 个解决方案

#1

Ouput parameter for one value:

一个值的输出参数：

EXEC CountAll @Total OUTPUT
SELECT ID, AddedDate, Title,Description,@total as Total
FROM dbo.Table1

Or use CROSS APPLY:

或者使用CROSS APPLY：

SELECT ID, AddedDate, Title,Description,@total as Total
FROM dbo.Table1
CROSS APPLY
(SELECT soemthing as total FROM .... WHERE...)

Or use a UDF

或者使用UDF

or a derived table

或派生表

SELECT ID, AddedDate, Title,Description, totals.Total
FROM
   dbo.Table1 T
   JOIN
   (SELECT count(*) as Total, id FROM dbo.Table1 GROUP BY ID) totals ON T.ID = totals.id

If the stored proc returns multiple columns, then you have to use a udf, cross apply, derived table or return 2 result sets to the client

如果存储的proc返回多个列，那么您必须使用udf，交叉应用，派生表或将2个结果集返回给客户端

Basically, stored procs can not be used the way you want

基本上，存储过程不能以您想要的方式使用

#2

SELECT ID, AddedDate, Title,Description,
       , ( Select Count(*)
            From OtherTable
            Where Table1.Id = OtherTable.Id ) As ItemCount
FROM dbo.Table1

You shouldn't need a stored procedure to simply get a count of items from another table. You can do it in a subquery.

您不需要存储过程来简单地从另一个表中获取项目数。您可以在子查询中执行此操作。

#3

You need a user defined function UDF instead of procedure. (If I remember well you can not call stored procedure from UDF)

您需要用户定义的函数UDF而不是过程。（如果我记得很清楚你不能从UDF调用存储过程）

Oher posibilities how to refactor your query are shown in aswer by gbn.

如何重构您的查询的其他可能性在gbn的aswer中显示。

#1