SQL stored procedures

SQL stored procedures

1. stored procedures without Parameters

2. Stored Procedures with input parameters

3. Stored Procedures with Input and Output Parameters

4. Stored Procedures with return values

Stored Procedure without Parameters

For example, the following stored procedure returns the records of all Employees in the Employees table.

Stored Procedure Code:

USE TSQLFundamentals2008; GOIF OBJECT_ID ('usp _ ProcDemoNoParam ', 'P') is not null drop proc usp_ProcDemoNoParam; GO -- 1, without the parameter create proc select * from hr. employees; ENDGO

Call code:

USE TSQLFundamentals2008; GO -- 1, call EXEC usp_ProcDemoNoParam without a parameter stored procedure;

Result:

We can see that nine records are returned.

Stored Procedure with input parameters

For example, the stored procedure accepts the input parameter @ empid and then returns the employee's information.

Code for creating a stored procedure:

IF OBJECT_ID ('usp _ ProcDemoWithInputParam ', 'P') is not null drop proc usp_ProcDemoWithInputParam; GO -- 2, with the input parameter create proc example @ empid as intasbegin select * from hr. employees WHERE empid = @ empid; ENDGO

Call:

-- 2. The input parameter stored procedure calls EXEC usp_ProcDemoWithInputParam @ empid = 5;

Result:

Stored Procedures with Input and Output Parameters

For example, the following stored procedure accepts @ empid (employee ID) as the input parameter, then returns the employee's information, and returns the number of affected lines of code as the output parameter.

Code for creating a stored procedure:

IF OBJECT_ID ('usp _ ProcDemoWithInputOutputParam ', 'P') is not null drop proc usp_ProcDemoWithInputOutputParam; GO -- 3, with input and output parameters create proc parameters @ empid as int, @ NumRowsAffected as int outputasbegin select * from hr. employees WHERE empid = @ empid; SET @ NumRowsAffected = @ ROWCOUNT; -- assign values. You can also use select to assign values to ENDGO.

Call:

-- 3. DECLARE @ nums as int; EXEC usp_ProcDemoWithInputOutputParam @ empid = 5, @ NumRowsAffected = @ nums OUTPUT; SELECT @ nums AS nums;

Result:

Stored Procedure with return value

For example, the following stored procedure accepts @ empid, that is, the employee ID, as the input parameter, and then judges whether the employee's record exists in the employee table. If yes, 1 is returned; otherwise, 0 is returned.

Code for creating a stored procedure:

IF OBJECT_ID ('usp _ procdemowithreturnvalue', 'P') is not null drop proc usp_ProcDemoWithReturnValue; GO -- 4, create proc usp_ProcDemoWithReturnValue @ empid as intasbegin if exists (SELECT * from hr. employees WHERE empid = @ empid) RETURN 1 else return 0; -- you can declare a variable and RETURN this variable ENDGO

Call:

-- 4: DECLARE @ status as int = 0; -- the default value is 0 EXEC @ status = dbo. usp_ProcDemoWithReturnValue @ empid = 5 -- intSELECT @ status AS thestatus;

Result:

SQL stored procedure

The benefit of stored procedures is that data operations are extremely fast,
In fact, those SQL statements have been compiled in the database.
The statements in the database are the SQL statements you want to use. And the variables you want to query are put in, some are equivalent to placeholder and so on, first placeholder. You just need to pass in the parameter and tell him to execute the Statement of the stored procedure.

What is a stored procedure? SQL stored procedure

Advantages of stored procedures: 1. Because the database is compiled before executing the action. However, the stored procedure is a compiled code block, so the execution efficiency is higher than the T-SQL statement. 2. A stored procedure can replace the large pile of T-SQL statements when the program interacts in the network, so it can also reduce the network traffic and improve the communication rate. 3. stored procedures allow unauthorized users to indirectly access the database under control to ensure data security. Summary: stored procedures are good things. They are essential tools for projects. The following describes the basic syntax of stored procedures. The stored procedure syntax and parameters explain some basic syntax of the stored procedure: -------------- CREATE a stored procedure --------------- create proc [EDURE] procedure_name [; number] [{@ parameter data_type} [VARYING] [= default] [OUTPUT] [,... n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [for replication] AS SQL _statement [... n] -------------- call Stored Procedure ----------------- EXECUTE Procedure_name ''-- if the stored procedure has parameters, the following parameter format is added: @ parameter name = value, you can also delete the Stored procedure ------------------------- drop procedure procedure_name directly as the parameter value. You can call another stored procedure in the stored procedure, but cannot delete another stored procedure.