Shulou(Shulou.com)06/01 Report--

Stored procedures-just like running methods (functions) in a database

Define

Is a set of precompiled SQL statements that perform specific functions

Is an object stored on the server

Can be called by object name

Like the method in C #, it consists of stored procedure name / stored procedure parameters / can have a return result.

The if else/while/ variables you learned earlier can be used in stored procedures.

Advantages:

Faster execution. Stored procedure statements saved in the database are compiled.

Allow modular programming

Improve system security and prevent sql injection

Reduce network circulation

System

Stored procedure

Defined by the system and stored in the master database

The name begins with "sp_" or "xp_"

Custom stored procedure

Stored procedures created by users in their own database

System stored procedure description

Sp_databases lists all databases on the server.

Sp_helpdb reports information about the specified database or all databases

Sp_renamedb changes the name of the database

Sp_tables returns a list of objects that can be queried in the current environment

Sp_columns returns the information of a table column

Sp_help looks at all the information about a table

Sp_helpconstraint looks at the constraints of a table

Sp_helpindex looks at the index of a table

Sp_stored_procedures lists all stored procedures in the current environment.

Sp_password adds or modifies the password for the login account.

Sp_helptext displays the actual text of default values, unencrypted stored procedures, user-defined stored procedures, triggers, or views.

Define the syntax of a stored procedure

CREATE PROC [EDURE] stored procedure name

@ Parameter 1 data type = default value OUTPUT

@ Parameter n data type = default value OUTPUT

AS

EXEC procedure name [parameter]

Stored procedure calls with no parameters:

Exec pro_GetAge

There are two methods of calling stored procedures with parameters:

EXEC proGetPageData 60 no 55-in order

EXEC proGetPageData @ labPass=55,@writtenPass=60-- Parameter name

Guidelines for defining stored procedures

The name of the stored procedure must follow the naming rules for identifiers

All database objects can be created in a stored procedure, except for views, stored procedures, and triggers.

Once the object is created, it can be referenced within the procedure.

2100 parameters can be used in a stored procedure

As long as there is enough memory space, you can create any number of local variables in the stored procedure.

Stored procedures can reference temporary tables

.

Rename stored procedure

Sp_rename oldname,newname

Modify stored procedure

Alter procedure proc_name

As

Sql_statement

Delete stored procedure

Drop procedure proc_name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.