Calling stored procedure from another stored procedure and returning result as new columns

Question

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?

Answer 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:

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

Or use a 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

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

Answer 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.

Answer 3

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

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