Summary of SQL Server Stored Procedure return values

1. when no return value is returned for a stored procedure (that is, no return or other statements are included in the Stored Procedure statement), use the int COUNT = executenonquery (...) method (..) the returned values of a stored procedure are only two cases (1) if the stored procedure is executed by the query analyzer, and if the number of affected rows is displayed in the display column, the number of rows of count is affected (2) if the stored procedure is executed through the query analyzer, in the display bar, if the 'COMMAND has been successfully completed. 'Then count =-1; if there is a query result in the display column, Count =-1 Summary:. executenonquery () This method returns only the number of affected rows. If the number of affected rows is not affected, the return value of this method can only be-1, not 0. B. Whether the executenonquery () method is executed according to commandtype. storedprocedure or commandtype. text, the effect is the same as that of. Certificate -------------------- 2. obtain the return value of a stored procedure-obtained through the query analyzer (1) stored procedure without any parameters (the Stored Procedure statement contains return) --- create procedure testreturn as return 145 go --- execute the Stored Procedure declare @ RC int exec @ rc = testreturn select @ RC --- the query result is 145 (2) stored Procedures with input parameters (stored procedure statements contain return) --- 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 --- execute the Stored Procedure <1> execute the following statements, and return 1 declare @ count int exec @ COUNT = sp_add_table1 '', '中 3 ', '000000' select @ count <2> execute the following statements, and return 0 declare @ count int exec @ COUNT = sp_add_table1 '', 'Middle 3 road ', '200' select @ count --- indicates that the query result is either 0 or 1 (3) a stored procedure with an output parameter (return can be included in the stored procedure without return). Example: --- create procedure sp_output @ output int output as set @ output = 121 return 1 --- execute the Stored Procedure <1> run the following, returns 121 declare @ out int exec sp_output @ out output select @ out <2>, return 1 declare @ out int declare @ count int exec @ COUNT = sp_output @ out output select @ count --- indicates that return exists, as long as the output parameter is queried, the query result is the final value of the output parameter in the stored procedure. If no output parameter is queried, the query result is the value returned by return. Example B: --- create procedure sp_output @ output int output as set @ output = 121 --- execute the Stored Procedure <1> run the following, returns 121 declare @ out int exec sp_output @ out output select @ out <2>, returns 0 declare @ out int declare @ count int exec @ COUNT = sp_output @ out output select @ count --- indicating that no return is returned, as long as the output parameter is queried, the query result is the final value of the output parameter in the stored procedure. If no output parameter is queried, the query result is 0. Conclusion: (1) there are three types of Stored Procedure:. the stored procedure ----------------------------- returned record set is a record set. For example, retrieve record B that meets one or more conditions from the database. return the stored procedure of a value (also called a scalar Stored Procedure) ----- It returns a value after execution, for example, executing a function or command C with a returned value in the database. behavior storage process ----------------------------------- is used to implement a function of the database without returning values. For example: update and delete operations in the database (2) the return value of a stored procedure containing return is the value returned by return (3) a stored procedure without return. The return value is 0 (4) regardless of whether the execution result has a record set) stored Procedure with output parameters: if return is returned, return the value returned by return is returned. If select is required, the value of the output parameter is displayed, and there is no return irrelevant parameter ------------------ 3. obtain the returned value of a stored procedure.Program Obtain parameter -------------------- sqlparameter [] parameter parms = {.., new sqlparameter ("@ return", sqldbtype. INT)}; partition parms [partition parms. length-1]. direction = parameterdirection. returnvalue; or parse parms [parse parms. length-1]. direction = parameterdirection. output or partition parms [partition parms. length-1]. direction = parameterdirection. input; get the returned object bj = partition parms [partition parms. length-1]. value;