1 format editing CREATE PROCEDURE [owner.] stored procedure name [; program number] stored procedures in SQL and related descriptions [(Parameter #,... Parameter #1024)][with{recompile | Encryption | RECOMPILE, encryption}][for replication]as program line where the stored procedure name cannot exceed 128 characters. Up to 1024 parameters per stored procedure (SQL Server 7.0 or later), parameters are used as follows: @ parameter name data type [VARYING] [= default value] [OUTPUT] There should be an "@" symbol before each parameter name. The parameters for each stored procedure are used internally for that program only, and the type of the parameter is available in addition to the image, which is supported by other SQL Server 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:
| 12345678 |
CREATEPROCEDUREorder_tot_amt@o_idint,@p_totintoutputASSELECT@p_tot=sum(Unitprice*Quantity)FROMorderdetails[email protected]_idGO |
Example: This example establishes 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.2 functional editing This type of language mainly provides the following functions, so that users can design a program that meets the application requirements: 1), Variable description 2), ANSI (National Standardization Organization) compatible SQL commands (such as select,update ...) 3), General Process Control commands (If...else ..., while ...). ) 4), intrinsic function3 kinds of editions
1 system stored proceduresStart with sp_ to make the system settings. Get information. Related management work.
2 Local stored proceduresA 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.
3 Temporary stored proceduresis divided into two kinds 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 in the tempdb database, and only the user who created it can execute it, and the global temporary stored procedure to two well font size (# #) Begins, the stored procedure becomes a global temporary stored procedure stored in the tempdb database, and once the global temporary stored procedure is created, any user who connects to the server later can execute it and does not require specific permissions.
4 remote stored proceduresIn 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.
5 Extended stored ProceduresExtended 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_.
stored procedure (SQL)
