General contents of the stored procedure

1 stored procedure queries in SQL Server allSelect COUNT (*)From sys.objectswhere type=‘P‘Where "P" represents a stored procedure (procedure).234The concept of a stored procedure56A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored in a database, executed by an application through a call, and allows the user to declare variables, conditional execution, and other powerful programming features.78stored procedures in SQL Server are divided into two categories: system-provided stored procedures and user-defined stored procedures.910There are several advantages to using stored procedures:11121. You can execute a series of SQL statements in a single stored procedure.13142You can reference other stored procedures from within your own stored procedure, which simplifies a series of complex statements.15163The stored procedure is compiled on the server at the time of creation, so it executes faster than a single SQL statement and reduces the burden of network traffic.17184Higher security, use of parameters, natural avoidance of SQL injection.1920Ii. Creating a stored procedure21st22Before we use SQL statements to create stored procedures, there are a few things to consider:23241, you cannot combine the CREATE PROCEDURE statement with other SQL statements into a single batch.25262, stored procedures can be nested, and the maximum nesting depth cannot exceed 32 layers.27283, the permission to create a stored procedure belongs to the database owner by default, and the owner can grant this permission to other users.29304, a stored procedure is a database object whose name must conform to the rules for identifiers.31325, you can only create stored procedures in the current database.33346, the maximum size of a stored procedure is 128M.3536The syntax for creating a stored procedure is as follows:37383940Code41CREATE PROCEDURE Stored Procedure name42（43 parameter 1 Parameter type =Parameter value parameter direction44 parameter 2 Parameter type =Parameter value parameter direction45) as46Begin47Stored Procedure Body RETURN48End4950515253Let's use the example below to show how to create a stored procedure:54555657Code58Use Northwindgo59/*After the stored procedure is created, its name is stored in the system table sysobjects, its source code is stored in the system table syscomments, so before creating the stored procedure, first determine whether the system has already created the stored procedure, if created, then delete the stored procedure.*/If Exists (SELECT *From sysobjectswhere Name= ' myprocedure ' and type=' P ')61Drop procedure Myprocedure;62GO63Create Proc Myprocedure(@SupplierID_2Int,@CategoryID_3Int,nvarchar @ProductName_1 (40) =‘No',--The default value for this parameter is ' none ' @ProductName_2 nvarchar (40) output)67As68Begin Insert into Products (Productname,supplierid,categoryid)69VALUES (@ProductName_1, @SupplierID_2, @CategoryID_3);70Select @ProductName_2 = ProductNameFromProducts71where SupplierID =@SupplierID_1; end72GO7374757677 Note:1. Statement body, use set to assign a value to the parameter.78792. The parameters of all variables of the stored procedure need to use "@" as the starting character;80813. Variables can be declared in the stored procedure and can be looped, as follows:82838485Code86Create procedure Batchaddclass87As88BeginDeclare @countIntSet @count =Ten while @count >091BeginInsert into Tbclass (ClassName) Values (@count) Set @[email protected]193End94Return95End969798The above statement realizes the bulk inserting 10 records into the Tbclass table;99100101102Third, execute the stored procedure103104The direct execution stored procedure can be executed using the Execute command, and the following example is used to execute the stored procedure:105106107108109declare @product nvarchar (40)111 EXEC Myprocedure1,1001, @product output112Select‘Product Name' =@product113Go114115116117118Use system stored procedures to view user-created stored procedures, the system stored procedures that are available for use, and their syntax in the following ways:119120sp_help: Parameters for displaying stored procedures and their data types121122sp_help [Name]123124The argument name is the name of the stored procedure that you want to view.125126Sp_helptext: The source code used to display stored procedures127128sp_helptext [Name]129130The argument name is the name of the stored procedure that you want to view.131132Sp_depends: Used to display and store procedure-related database objects133134sp_depends [Name]135136The parameter name is the name of the stored procedure for which you want to view dependencies.137138Sp_stored_procedures: Used to return a list of stored procedures in the current database139140141142Iv. Modifying stored procedures143144Stored procedures can change based on user requirements or changes in the base table definition. Use the ALTER PROCEDURE statement to change the process that was previously created by executing the Create PROCEDURE statement without changing the permissions or affecting the associated stored procedure or trigger. The method of modifying the stored procedure is the same as the method for creating the stored procedure, except that the CREATE procedure that created the stored procedure is changed to alter procedure.145146147148V. Renaming and deleting stored procedures1491501. Renaming stored Procedures151152You can modify the name of a stored procedure by using the system stored procedure sp_rename, which has the following syntax:153154sp_rename the original stored procedure name, new stored procedure name155156In addition, the name of the stored procedure can also be modified through Enterprise Manager.1571581591602. Delete a stored procedure161162Delete stored procedures you can use the drop command, and the drop command can delete one or more stored procedures or stored procedure groups from the current database in the following syntax:163164drop procedure {Procedure} [,... N]165166Of course, it is also easy to remove stored procedures with Enterprise Manager.167168169170Vi. recompilation of stored procedures171172After we have used a stored procedure for some reason, you may have to change the logical structure of the database by adding new data columns to the table or new indexes for the table. At this point, the stored procedure needs to be recompiled, and SQL Server provides three ways to recompile the stored procedure:1731741, set recompile when setting up a stored procedure175176Syntax format:177178CREATE PROCEDURE procedure_name179With RECOMPILE as Sql_statement1802, setting a recompile when executing a stored procedure181182Syntax format:183184EXECUTE procedure_name with RECOMPILE1851861873, setting up recompilation by using system stored procedures188189Syntax format190191EXEC sp_recompile OBJECT192193194Vii. system stored procedures and extended stored procedures1951961. system Stored Procedures197198system stored procedures are stored in the master database and prefixed with sp_, which is used primarily to obtain information from system tables, to help administrators manage SQL Server, and to facilitate user viewing of database objects. For example, the system stored procedures used to view database object information sp_help, stored procedures that display stored procedures and other objects ' text, sp_helptext, and so on.199 200 201 202 2203 Span style= "color: #008080;" The >204 extended stored procedure, prefixed with XP_, is part of the Open Data Services layer of the relational database engine, which enables the user to implement logic in the functions contained in a dynamic-link library (DLL) file, extending the functionality of Transact-SQL. These functions can be called from Transact-sql statements, as in the case of a Transact-SQL procedure. 205 206 207 208  use master 209 exec xp_cmdshell  "dir *.exe '                 

General contents of the stored procedure