Create and call a stored procedure in My SQL Server

Advantages of creating and calling stored procedures in My SQL Server Stored Procedures: ① fast execution speed ② templated Program Design ③ reduce network traffic ④ provide system security. what is stored procedure stored Procedure (stored procdures) is a set of pre-compiled SQL query statements and control flow statements, and is saved in the database with a specific name. There are also custom stored procedures and system stored procedures-system stored procedures → system stored procedures are named starting with "sp _" and starting with "xp. In the master database → extend the stored procedure: xp_mongoshell, you can call the doscommand usage: exec xp_mongoshell doscommand [no_output] → execute is used to call the stored procedure, which can also be abbreviated as exec Syntax: execute 'stored procedure name' 'parameter' -- if no parameter exists, the parameter is omitted. User-Defined Stored Procedure Syntax: create procedure stored procedure name @ parameter 1 Data Type [= default value] [parameter type (input/output)]... @ parameter n data type [= default value] [parameter type (input/output)] as SQL statement... go * Note: The parameter types include input and output parameters. The default value is input parameters. output indicates output. It is best to start the stored procedure with proc. ① Create a stored procedure without parameters: Query all idle computer information -- determine whether the stored procedure exists if exists (select * from sysobjects where [name] = 'proc _ getpc ') drop procedure proc_GetPcgo -- create procedure proc_GetPc without parameters -- no parameter asselect pcId as 'computer number ', 'computer usage status' = case when PcUse = 0 then' idle 'when PcUse = 1 then' idle 'pcnote as 'note' from PcInfo where PcUse = 0go -- call the Stored Procedure execute proc_GetPc ② create a stored procedure syntax with input parameters: create procedure stored procedure name @ parameter 1 Data Type [= default value]... @ parameter N data type [= Mo Value recognition] as SQL statement... go ③ the stored procedure with output parameters is similar to the C language return value. After the stored procedure is executed, a value can be returned. However, unlike C, a stored procedure can return only one or more values. This requires us to define and receive output parameters. ④ Use the return keyword of return to terminate the execution of the stored procedure or return data. Similar to return in C functions. Return can only return int type, and output can return any type. · Handle error information: Use raiserror in SQL Server to return user-defined error information. Syntax: RAISERROR (custom error message, error severity, and error status) → custom error message: indicates the output error prompt text. → Error severity level: indicates the Severity Level of custom errors. → Error status: indicates the custom error status. The value range is 1-127. The stored PROCEDURE is a database object. You can use the drop procedure statement to delete the stored PROCEDURE.