Stored procedure is a set of SQL statements to complete specific functions. It is compiled and stored in the server-side database. Users execute it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).
(1) System stored procedures: stored procedures provided by SQL Server system are mainly stored in master database and user database, and mainly realize some system management functions and database object management functions.
(2) User defined stored procedure: a stored procedure created by the user according to needs to realize user specific applications.
3. Advantages of stored procedures(1) Stored procedures can realize component management
Stored procedure is a program body to realize specific functions. Different applications can call stored procedures through names and parameters. The modification and improvement of stored procedures will not affect the application and improve the portability of the system.
(2) Stored procedures can achieve faster execution speed
Because stored procedures are pre compiled and optimized program code.
(3) Stored procedures can reduce network traffic
The client program calls the stored procedure by name and parameters instead of passing the entire TSQL code to perform the operation.
(4) Stored procedures can achieve data security
The calling of stored procedure requires permission, and the operation on data is encapsulated, and only the calling interface is provided.
4. Create stored procedure with TSQL commandCREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }[ VARYING ] [ = default ] [ OUTPUT ]] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] | [ FOR REPLICATION ] AS sql_statement [ ...n ]2, Instance
Add supplier parts supply table SP table and order table Orders table;
Create a sales stored procedure, insert an order record into the order table, and modify the inventory balance in the part supply table, where the order time takes the system time when the order is inserted (using the getdate() function). Call the sales stored procedure to add the information of each transaction to the database.
USE SPJDATABASE GO --Create supplier parts supply table SP Form, order form Orders surface CREATE TABLE SP( SNO CHAR(10), PNO CHAR(10), balance int CHECK(balance >= 0), FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), ); CREATE TABLE Orders( ONO CHAR(10), SNO CHAR(10), PNO CHAR(10), JNO CHAR(10), Otime DATETIME NOT NULL DEFAULT GETDATE() , --System time when inserting order quantity int CHECK(quantity >= 0), PRIMARY KEY (Ono), FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO), ); GO INSERT INTO SP VALUES('S1','P1',1000); INSERT INTO SP VALUES('S1','P2',1000); INSERT INTO SP VALUES('S1','P3',1000); INSERT INTO SP VALUES('S1','P4',1000); INSERT INTO SP VALUES('S1','P5',1000); INSERT INTO SP VALUES('S1','P6',1000); INSERT INTO SP VALUES('S2','P1',1000); INSERT INTO SP VALUES('S2','P2',1000); INSERT INTO SP VALUES('S2','P3',1000); INSERT INTO SP VALUES('S2','P4',1000); INSERT INTO SP VALUES('S2','P5',1000); INSERT INTO SP VALUES('S2','P6',1000); INSERT INTO SP VALUES('S3','P1',1000); INSERT INTO SP VALUES('S3','P2',1000); INSERT INTO SP VALUES('S3','P3',1000); INSERT INTO SP VALUES('S3','P4',1000); INSERT INTO SP VALUES('S3','P5',1000); INSERT INTO SP VALUES('S3','P6',1000); INSERT INTO SP VALUES('S4','P1',1000); INSERT INTO SP VALUES('S4','P2',1000); INSERT INTO SP VALUES('S4','P3',1000); INSERT INTO SP VALUES('S4','P4',1000); INSERT INTO SP VALUES('S4','P5',1000); INSERT INTO SP VALUES('S4','P6',1000); INSERT INTO SP VALUES('S5','P1',1000); INSERT INTO SP VALUES('S5','P2',1000); INSERT INTO SP VALUES('S5','P3',1000); INSERT INTO SP VALUES('S5','P4',1000); INSERT INTO SP VALUES('S5','P5',1000); INSERT INTO SP VALUES('S5','P6',1000); GO IF OBJECT_ID ( 'INSERT_ORDERS', 'P' ) IS NOT NULL DROP PROCEDURE INSERT_ORDERS; GO CREATE PROCEDURE INSERT_ORDERS @ONO CHAR(10), @SNO CHAR(10), @PNO CHAR(10), @JNO CHAR(10), @quantity int AS BEGIN DECLARE @ErrorVar INT; --Declare user variables,For storage SQL Error number of the statement BEGIN TRANSACTION; INSERT INTO Orders (ONO,SNO,PNO,JNO,quantity) VALUES(@ONO,@SNO,@PNO,@JNO,@quantity) UPDATE SP SET balance = balance -@quantity WHERE PNO=@PNO AND SNO=@SNO; --Determine previous SQL The execution state of the statement SELECT @ErrorVar = @@ERROR; --System variable, previous sentence SQL Execution status of, --After the meeting, he was next SQL Statement, so save as IF @ErrorVar != 0 --A value of 0 indicates that the code is executed correctly, and a value other than 0 indicates the system defined error number, --The detailed error information can be viewed and handled in the main calling program BEGIN ROLLBACK; --Will reset@@ERROR，Therefore, it is used in the front@ErrorVar Save as RETURN @ErrorVar; END COMMIT; RETURN 0; END GO --call/Execute stored procedure DECLARE @retstat int; --Execution status EXECUTE @retstat = INSERT_ORDERS 'O1','S1','P1','J1',100 SELECT @retstat IF @retstat = 0 SELECT 'Insert succeeded.' ELSE SELECT 'Insert failed.'