SQL Server Database -- creation and use of stored procedures

1, Stored procedure

      1. Stored procedure

        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).

      2. Classification of stored procedures

      (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 command

CREATE 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.'

​​​​​​

Tags: Database SQL Server

Posted on Sun, 28 Nov 2021 22:43:57 -0500 by BigMike