Shulou(Shulou.com)05/31 Report--

What is the basis of stored procedures in SQL Server? for this question, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

You can create a procedure for permanent use, or temporarily use it in one session (local temporary procedure), or temporarily in all sessions (global temporary procedure). You can also create stored procedures that run automatically when Microsoft SQL Server starts. Syntax CREATE PROC [EDURE] procedure_name [; number] [{@ parameter data_type} [VARYING] [= default] [OUTPUT]] [,... n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR REPLICATION] AS sql_statement [... n] the name of the parameter procedure_name new stored procedure. The procedure name must conform to the rules for identifiers and must be unique to the database and its owner. For more information, see using identifiers. To create a local temporary procedure, you can add a symbol (# procedure_name) before the procedure_name, and to create a global temporary procedure, you can add two symbols (# # procedure_name) before the procedure_name. The full name (including # or #) cannot exceed 128 characters. Specifies that the name of the process owner is optional. Number is an optional integer used to group procedures of the same name so that procedures of the same group can be removed with a DROP PROCEDURE statement For example, the procedures used by an application named orders can be named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement removes the entire group. If the name contains a delimiter, the number should not be included in the identifier, only the appropriate delimiter should be used before and after the procedure_name. Parameters in the @ parameter procedure. You can declare one or more parameters in a CREATE PROCEDURE statement. The user must provide a value for each declared parameter when executing the procedure (unless a default value for that parameter is defined). A stored procedure can have up to 2.100 parameters. Use the @ symbol as the first character to specify the parameter name. The parameter name must conform to the rules for identifiers. The parameters of each procedure are used only for the process itself; the same parameter name can be used in other procedures. By default, parameters can only replace constants, not table names, column names, or other database object names. For more information, see EXECUTE. The data type of the data_type parameter. All data types, including text, ntext, and image, can be used as parameters to stored procedures. However, the cursor data type can only be used with the OUTPUT parameter. If the specified data type is cursor, you must also specify both the VARYING and OUTPUT keywords. For more information about the data types provided by SQL Server and their syntax, see data types. Indicates that there is no maximum number of output parameters that can be cursor data types. VARYING specifies the result set that is supported as an output parameter (dynamically constructed by the stored procedure and the content can be changed). Applies only to cursor parameters. The default value of the default parameter. If you define a default value, you do not have to specify a value for that parameter to execute the procedure. The default value must be constant or NULL. If the procedure will use the LIKE keyword for this parameter, the default value can include wildcards (%, _, [], and [^]). OUTPUT indicates that the parameter is a return parameter. The value of this option can be returned to EXEC [UTE]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameter using the OUTPUT keyword can be a cursor placeholder. Represents a placeholder that can specify up to 2.100 parameters. {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} RECOMPILE indicates that SQL Server will not cache the plan for the process, which will be recompiled at run time. Use the RECOMPILE option when using atypical or temporary values and do not want to overwrite an execution plan that is cached in memory. ENCRYPTION represents an entry in the SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the process from being published as part of SQL Server replication. Indicates that during the upgrade process, SQL Server uses the encryption comments stored in syscomments to recreate the encryption process. FOR REPLICATION specifies that stored procedures created for replication cannot be executed at the subscriber. . Stored procedures created with the FOR REPLICATION option can be used as stored procedure filtering and can only be executed during replication. This option cannot be used with the WITH RECOMPILE option. AS specifies the action to be performed by the procedure. Any number and type of Transact-SQL statements to be included in the sql_statement procedure. But there are some restrictions. Is a placeholder that indicates that this procedure can contain multiple Transact-SQL statements. The maximum size of the comment stored procedure is 128 MB. User-defined stored procedures can only be created in the current database (except for temporary procedures, which are always created in tempdb). CREATE PROCEDURE statements cannot be combined with other Transact-SQL statements in a single batch. By default, the parameter can be empty. If you pass the value of the NULL parameter and the parameter is used in a CREATE or ALTER TABLE statement, and the column referenced in the statement does not allow NULL, SQL Server generates an error message. To prevent passing NULL parameter values to columns that are not allowed to use NULL, add programming logic to the procedure or use default values for the column (using the DEFAULT keyword of CREATE or ALTER TABLE). It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement of a stored procedure, such as when creating a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control how SQL Server assigns NULL or NOT NULL attributes to columns, if not specified in the CREATE TABLE or ALTER TABLE statement. If a stored procedure executed by a connection has different settings for these options than the connection that created the procedure, the table columns created for the second connection may have different nullability and behave differently. If NULL or NOT NULL is explicitly declared for each column, the same nullability temporary table is created for all connections that execute the stored procedure. SQL Server saves settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS when you create or change a stored procedure. These original settings are used when the stored procedure is executed. Therefore, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings for all client sessions are ignored when the stored procedure is executed. The SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that appear in a stored procedure do not affect the functionality of the stored procedure. Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS, are not saved when the stored procedure is created or changed. If the logic of the stored procedure depends on a specific setting, add a SET statement at the beginning of the procedure to ensure that the setting is correct. When a SET statement is executed from a stored procedure, this setting is valid only until the stored procedure completes. After that, the setting is restored to the value when the stored procedure was called. This allows individual clients to set the desired options without affecting the logic of the stored procedure. Indicates whether SQL Server interprets an empty string as a single space or as a true empty string, controlled by the compatibility level setting. If the compatibility level is less than or equal to 65 Server SQL SQL interprets an empty string as a single space. If the compatibility level equals 70, SQL Server interprets an empty string as an empty string. For more information, see sp_dbcmptlevel. To get information about a stored procedure, to display the text used to create the procedure, execute sp_helptext in the database where the procedure resides and use the procedure name as a parameter. Indicates that stored procedures created with the ENCRYPTION option cannot be viewed using sp_helptext. To display a report on objects referenced by a procedure, use sp_depends. To rename a procedure, use sp_rename. The reference object SQL Server allows stored procedures to be created to reference objects that do not already exist. At creation time, only syntax checking is performed. At execution time, if there is no valid plan in the cache, the stored procedure is compiled to generate the execution plan. All objects referenced in the stored procedure are resolved only during compilation. Therefore, if a syntactically correct stored procedure references an object that does not exist, it can still be successfully created, but will fail at run time because the referenced object does not exist. For more information, see deferred name resolution and compilation. Deferred name resolution and compatibility level SQL Server allow Transact-SQL stored procedures to reference tables that do not exist at creation time. This capability is called deferred name resolution. However, if the Transact-SQL stored procedure references the table defined in the stored procedure, and the compatibility level setting (set by executing sp_dbcmptlevel) is 65, a warning message is issued at creation time. If the table referenced at run time does not exist, an error message is returned. For more information, see sp_dbcmptlevel and deferred name resolution and compilation. After the stored procedure executes the CREATE PROCEDURE statement successfully, the procedure name is stored in the sysobjects system table, and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the process is compiled to determine the best access plan for retrieving data. Parameter stored procedures that use cursor data types can only use cursor data types for OUTPUT parameters. If you specify the cursor data type for a parameter, you must also specify the VARYING and OUTPUT parameters. If the VARYING keyword is specified for a parameter, the data type must be cursor, and the OUTPUT keyword must be specified.

This is the answer to the question about what is the basis of stored procedures in SQL Server. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.