How to obtain the return values of SQL statements or stored procedures after exec in SQL Server

In the process of database program development, we often encounter the use of exec to execute an SQL statement that needs to return certain values (usually used to construct dynamic SQL statements ), or, in a stored procedure, you can use exec to call another stored procedure with a returned value (the returned value must be obtained). How can you obtain the returned values?

1. Execution of SQL statements by 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

The above @ rc SQL statement is used to find out the number of free classrooms in a specific period of time and the number of students that these classrooms can accommodate, because it involves the structure of dynamic SQL statements (the condition in @ csql contains a column name that changes dynamically), it should be executed in exec, but at the same time I have to return two results, therefore, the code for execution is:

Exec sp_executesql @ RC, n' @ A int output, @ B INT output', @ cstucount output, @ ccount output -- put exec results into Variables

In this way, the returned values are put in the @ cstucount and @ ccount variables to get the expected results.

2. Exec execution of stored procedures with returned values

Let's look at a simple stored procedure:

Create procedure protest
(
@ Name varchar (10 ),
@ Money int output
)
As
Begin
If (@ name = '1 ')
Set @ money = 1000
Else
Set @ money = 2000
End

This is just a simple example. This stored procedure returns the value of @ money. How can we obtain this parameter when we call this stored procedure in another stored procedure, the method is as follows:

Declare @ M int --- the variable used to receive the returned value
Exec protest @ name = '1', @ money = @ M output -- note that the name is output.

In this simple way, we get the returned value, and then we can use it.
Haha... This is the solution I encountered when I was working on the project (of course there may be other solutions )... Hope to help you...