Introduction of MSSQL stored procedure and application of example
/*
First look at the MSSQL stored procedure creation
CREATE PROCEDURE Proc_stu
As
SELECT * FROM Student
Go
To create a process: an example
The human Resources program in the schema created by the following statement remove_emp:
CREATE PROCEDURE remove_emp (employee_id number) as
Tot_emps tutorial number;
BEGIN
DELETE from Employees
WHERE employees.employee_id = remove_emp.employee_id;
Tot_emps: = tot_emps-1;
End;
Simple examples of stored procedures look, then let's look at the syntax
CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number]
[{@parameter [type_schema_name.] Data_type}
[Varying] [= default] [out | OUTPUT] [READONLY]
] [,... N]
[With <procedure_option> [,... N]]
[For REPLICATION]
as {<sql_statement> [;] [... n] | <method_specifier>}
[;]
<procedure_option>:: =
[Encryption]
[RECOMPILE]
[EXECUTE as Clause]
<sql_statement>:: =
{[BEGIN] statements [end]}
<method_specifier>:: =
EXTERNAL NAME Assembly_name.class_name.method_name
Okay, let's take a look at an instance of an example application,
Instance of a stored procedure with query ID 1
@Total int OUTPUT
-----------------------------------
SET @Sql =n ' select A,b,c,d from t where Id=1 '
Exec sp_executesql @Sql, N ' @Total Int out ', @Total out
-----------------------------------
return @Total
Example Three
Add a record to the table book and inquire about the total amount of all books in this table
Create proc Insert_book
@param1 Char, @param2 varchar, @param3 money, @param4 money output
With encryption---------encryption
As
Insert Book (number, title, price) Values (@param1, @param2, @param3)
Select @param4 =sum (price) from book
Go
Example of execution:
DECLARE @total_price Money
EXEC insert_book ' 003 ', ' Delphi Control Development Guide ', $, @total_price
print ' Total amount is ' +convert (varchar, @total_price)
Go
*/