Stored Procedures (2)

Basic TerminologyFoldedSystem Stored Procedures

Start with sp_ to make the system settings. Get information. Related management work.

FoldedLocal stored Procedures

A stored procedure created by a user is a stored procedure that is created by a user and completes a particular function, in fact a stored procedure that is referred to as a local stored procedure.

FoldedTemporary stored procedures

is divided into two types of stored procedures:

One is a local temporary stored procedure, with a well font size (#) as the first character of its name, the stored procedure becomes a local temporary stored procedure that resides in the tempdb database and can only be executed by the user who created it;

Second, the global temporary stored procedure, starting with two well font size (# #), the stored procedure becomes a global temporary stored procedure stored in the tempdb database, once the global temporary stored procedure is created, it can be executed by any user who connects to the server later, and does not require specific permissions.

FoldedRemote stored Procedures

In SQL Server2005, remote stored procedures (Stored procedures) are stored procedures located on a remote server, and you can typically execute a remote stored procedure using the distributed query and execute commands.

FoldedExtended stored Procedures

Extended stored Procedures (Extended Stored procedures) are stored procedures that users can write in an external program language, and the name of an extended stored procedure usually begins with a xp_.

FoldedEdit Other information in this paragraphFoldedformat

CREATE PROCEDURE [owner.] stored procedure name [; program number] stored procedures in SQL and related introduction

[Parameter #,... Parameter #1024)]

[With

{RECOMPILE | Encryption | RECOMPILE, encryption}

]

[For REPLICATION]

As Program line

Where the stored procedure name cannot exceed 128 characters. Set up to 1024 parameters per stored procedure

(SQL Server 7.0 or later), the parameters are used as follows:

@ Parameter name data type [VARYING] [= default value] [OUTPUT]

Each parameter name must have an "@" symbol before it, and the parameters for each stored procedure are used internally only for that program, and the type of the parameter is available in addition to the image, which is supported by other SQL Server data types.

[= default value] is equivalent to setting a value for a field when setting up the database, here is the default value for this parameter. [Output] is used to specify that the parameter is both an input and an output value, that is, when the stored procedure is called, if the specified parameter value is the parameter that we need to input, but also need to output in the result, the item must be an output, and if it is only used for outputting parameters, you can use the cursor , and when you use this parameter, you must specify both the varying and the output statements.

Example:

CREATE PROCEDURE Order_tot_amt

@o_id int,

@p_tot int Output

As

SELECT @p_tot = SUM (unitprice*quantity)

From OrderDetails

WHERE [Email protected]_id

GO

Example Description:

The example is to create a simple stored procedure Order_tot_amt, which calculates the total sales [unit Price (UnitPrice) * Quantity of the order (OrderDetails) according to the Order ID number (@o_id) entered by the user ( Quantity)], this amount is output through the @p_tot parameter to the program that called the stored procedure.

Stored Procedures (2)