Use SQL Server Query Analyzer to get the return value of the stored procedure, check the test stored procedure

1. a case where the stored procedure does not return a value ( that is, a statement in a stored procedure statement that does not have a return )
Using the method int count = ExecuteNonQuery (..) There are only two cases of executing a stored procedure whose return value
(1) If the stored procedure is executed through the Query Analyzer, the number of rows affected in the display bar is affected by a few rows count
(2) If the stored procedure is executed through the Query Analyzer, the Display column displays ' command completed successfully. ' Count =-1; If there is a query result in the display bar, Count =-1
Summary : A.executenonquery () The method returns only the number of rows affected, and if it does not affect the number of rows, the return value of the method can only be-1, not 0.
B. Whether the ExecuteNonQuery () method is performed according to CommandType.StoredProcedure or CommandType.Text, the effect is the same as a.

2. Gets the return value of the stored procedure - - obtained by the query parser
(1) Stored procedure without any parameters (the stored procedure statement contains a return)

---Create a stored procedure
CREATE PROCEDURE Testreturn
As
return 145
GO
---Executing stored procedures
DECLARE @RC int
EXEC @RC =testreturn
Select @RC
---description
Query result is 145

(2) Stored procedure with input parameters (the stored procedure statement contains a return)

---Create a stored procedure
CREATE PROCEDURE Sp_add_table1
@in_name varchar (100),
@in_addr varchar (100),
@in_tel varchar (100)
As
if (@in_name = ' or @in_name is null)
Return 1
Else
Begin
Insert INTO table1 (Name,addr,tel) VALUES (@in_name, @in_addr, @in_tel)
return 0
End
---Executing stored procedures
<1> perform the following, return 1
DECLARE @count int EXEC @count = sp_add_table1 ", ' Middle three ', ' 123456 ' select @count
<2> perform the following, return 0
DECLARE @count int EXEC @count = sp_add_table1 ", ' Middle three ', ' 123456 ' select @count
---description
The query results are either 0 or 1.

(3) Stored procedure with output parameters (there can be a return in the stored procedure without return)

example a:
---Creating a stored procedure
CREATE PROCEDURE sp_output
@output int output
as
Set @output = 121
Return 1
---Execute stored procedure
<1> execute the following to return 121
Declare @out int
exec sp_output @out output
Select @out
< 2> executes the following, returning 1
declare @out int
declare @count int
Exec @count = sp_output @out output
Select @count
---Description The
has a return, as long as the query output parameter, the query result is the output parameter in the stored procedure last changed value; As long as the output parameter is not queried, the query result is the value returned by return

Example B:
---Create a stored procedure
CREATE PROCEDURE Sp_output
@output int Output
As
Set @output = 121
---Executing stored procedures
<1> perform the following, return 121
DECLARE @out int
EXEC sp_output @out output
Select @out
<2> perform the following, return 0
DECLARE @out int
DECLARE @count int
exec @count = sp_output @out Output
Select @count
---description
No return, as long as the query output parameter, the query result is the output parameter in the stored procedure to the last value; As long as the output parameters are not queried, the query result is 0

Summarize:
(1) The stored procedure is divided into 3 categories:
A. Returning a stored procedure for a recordset---------its execution result is a recordset, for example: Retrieving records from a database that match one or several criteria
B. A stored procedure that returns a numeric value (also known as a scalar stored procedure)-----Returns a value after its execution, such as executing a function or command with a return value in the database
C. Behavior stored procedures---------used to implement a function of a database without a return value, for example: Update and delete operations in the database
(2) A stored procedure containing return whose return value is the value returned by return
(3) No return stored procedure, regardless of the execution result has no Recordset, its return value is 0
(4) A stored procedure with an output parameter: Returns the value returned if there is a return, and if a select output parameter is present, the value of the output parameter, regardless of if there is no return