MySQLServer stored procedure creation and calling _ MySQL

Create a MySQLServer stored procedure and call bitsCN.com

Create and call a stored procedure in My SQL Server

Advantages of stored procedures:

① Fast execution speed

② Templated program design

③ Reduce network traffic

④ Provides system security

. What is stored procedure

Stored Procedures (stored procdures) are a set of pre-compiled SQL query statements and control flow statements, and are saved in the database with a specific name. It can be divided into custom stored procedures and system stored procedures.

· System stored procedures

→ System stored procedure names start with "sp _" and start with "xp. In the master database

→ Extended stored procedure: xp_mongoshell, which can call the doscommand

The usage is as follows:

Exec xp_cmdshell doscommand [no_output]

→ Execute is used to call a stored procedure. It can also be abbreviated to exec.

Syntax:

Execute 'stored procedure name' 'parameter' -- If no parameter exists, the parameter is omitted.

· Custom stored procedures

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

Example:

Query information of all idle computers

-- Determine whether a stored procedure exists

If exists (select * from sysobjects where [name] = 'proc _ getpc ')

Drop procedure proc_GetPc

Go

-- Create a stored procedure without parameters

Create procedure proc_GetPc

-- No parameter

As

Select pcId as 'computer number ',

'Computer usage status' = case

When PcUse = 0 then 'id'

When PcUse = 1 then 'id'

PcNote as 'note'

From PcInfo where PcUse = 0

Go

-- Call a stored procedure

Execute proc_GetPc

② Create a stored procedure with input parameters

Syntax:

Create procedure stored procedure name

@ Parameter 1 data type [= default value]

...

@ Parameter N data type [= default value]

As

SQL statement

...

Go

③ Create a stored procedure with output parameters

Similar to the C language return value, a stored procedure can return a value. However, unlike C, a stored procedure can return only one or more values. This requires us to define and receive output parameters.

④ Use of return

The return keyword ends the execution of the stored procedure or returns data. Similar to return in C functions.

Return can only return int type, and output can return any type.

· Handle error messages

Use raiserror in SQL Server to return user-defined error messages.

Syntax:

RAISERROR (custom error message, error severity, and error status)

→ Custom error message: 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.

BitsCN.com