SQL: One Stored Procedure Calls another stored procedure to obtain the returned value

Method 1: Use the output parameter

Use adventureworks;
Go
If object_id ('production. usp_getlist', 'P') is not null
Drop procedure production. usp_getlist;
Go
Create procedure production. usp_getlist @ Product varchar (40)
, @ Maxprice money
, @ Compareprice money output
, @ Listprice money out
As
Select P. Name as product, P. listprice as 'list price'
From production. product P
Join production. productsubcategory s
On P. productsubcategoryid = S. productsubcategoryid
Where S. name like @ Product and P. listprice <@ maxprice;
-- Populate the output variable @ listprice.
SET @ listprice = (select max (p. ListPrice)
FROM Production. Product p
JOIN Production. ProductSubcategory s
ON p. ProductSubcategoryID = s. ProductSubcategoryID
WHERE s. name LIKE @ product AND p. ListPrice <@ maxprice );
-- Populate the output variable @ compareprice.
SET @ compareprice = @ maxprice;
GO

When another stored procedure is called:

Create Proc Test
As
DECLARE @ compareprice money, @ cost money
EXECUTE Production. usp_GetList '% Bikes %', 700,
@ Compareprice OUT,
@ Cost OUTPUT
IF @ cost <= @ compareprice
BEGIN
PRINT 'these products can be purchased for less
$ '+ RTRIM (CAST (@ compareprice AS varchar (20) + '.'
END
ELSE
PRINT 'the prices for all products in this category exceed
$ '+ RTRIM (CAST (@ compareprice AS varchar (20) + '.'
Method 2: create a temporary table

Create proc GetUserName
As
Begin
Select 'username'
End

Create table # tempTable (userName nvarchar (50 ))
Insert into # tempTable (userName)
Exec GetUserName

Select # tempTable

-- Clear the temporary table after use
Drop table # tempTable -- note that this method cannot be nested. For example:

Procedure
Begin
...
Insert # table exec B
End

Procedure B
Begin
...
Insert # table exec c
Select * from # table
End

Procedure c
Begin
...
Select * from sometable
End

-- Here, a calls the result set of B, and B also has such an application B calls the result set of c, which is not allowed,
-- The "insert exec statement cannot be nested" error is reported. This type of application should be avoided in practical applications.

Method 3: declare a variable and execute it with exec (@ SQL:

1) Execute SQL statements using EXEC

Declare @ rsql varchar (250)
Declare @ csql varchar (300)
Declare @ rc nvarchar (500)
Declare @ cstucount int
Declare @ ccount int
Set @ rsql = '(select Classroom_id from EA_RoomTime where zc =' + @ zc + 'and xq =' + @ xq + 'and t' + @ time +' = ''No '') and ClassroomType = '1 '''
-- Exec (@ rsql)
Set @ csql = 'select @ a = sum (teststucount), @ B = sum (classcount) from EA_ClassRoom where classroom_id in'
Set @ rc = @ csql + @ rsql
Exec sp_executesql @ rc, n' @ a int output, @ B int output', @ cstucount output, @ ccount output -- put exec results into Variables
-- Select @ csql + @ rsql
-- Select @ cstucount