Database Integration-stored procedures

Stored procedures, such as the same programming language, also contain data types, process control, input and output, and their own function libraries. Stored procedures: (1) stored procedures are passed through parameters, which ensures high security and prevents injection attacks. (2) The query statement is irrelevant to the program in the stored procedure. If you want to modify the program or database in the future, no chain exists.

Stored procedures, such as the same programming language, also contain data types, process control, input and output, and their own function libraries. Stored procedures: (1) stored procedures are passed through parameters, which ensures high security and prevents injection attacks. (2) The query statement is irrelevant to the program in the stored procedure. If you want to modify the program or database in the future, no chain exists.

Stored procedures, such as the same programming language, also contain data types, process control, input and output, and their own function libraries.

Stored Procedure:

(1) The stored procedure is passed through parameters, which ensures high security and prevents injection attacks.

(2) The query statement is irrelevant to the program in the stored procedure. If you want to modify the program or database in the future, no chain reaction will occur, increasing system scalability.

(3) When performing a query on a website, you only need to pass simple parameters. Both code optimization and query optimization can be efficient.

(4) Modular programming is allowed, that is, a set of queries can be written in a process, and then called directly in the program without having to write several statements each time to implement the corresponding functions.

Use: data table source http://blog.csdn.net/buyingfei8888/article/details/17399837

1. Simple query of Stored Procedures

If exists (select 1 from sysobjects where id = object_id ('test') and xtype = 'P') -- determine whether the stored procedure has the name of the drop proc test; gocreate proc testasselect s_name store, s_address store address, c_name salesperson from t_shop, t_cash_housewoker where s_id in (select s_id from manage where m_id = 1) and t_cash_housewoker.m_id = 1

Run:

exec test

Sysobjects object_id:

1. sysobjects System Object table. Save the objects of the current database, such as constraints, default values, logs, rules, and stored procedures. In databases of sqlserver2005 and sqlserver2008, they are now used as a view object, in the system view of each database, there is a sys. sysobjects view object. Description of important sysobjects fields: sysObjects (Name sysname, -- object Name id int, -- object id xtype char (2), -- object type char (2 ), -- Object type (does it seem identical to xtype? A little depressing ...) Uid smallint, -- ID of the object owner... -- other fields are not often used. Note: The xtype and type are exactly the same. The data is as follows: C = CHECK constraint D = DEFAULT value or DEFAULT constraint F = foreign key constraint FN = scalar function IF = nested table function K = primary key or UNIQUE constraint L = log P = Stored Procedure R = rule RF = copy and filter Stored Procedure S = system table TF = table function TR = trigger U = User table V = view X = Extended Stored Procedure AF = aggregate function (CLR) FS = assembly (CLR) scalar function FT = assembly (CLR) Table value function IF = inline table function IT = internal table PC = assembly (CLR) stored Procedure PK = primary key constraint (type: K) SN = synonym SQ = Service Queue TA = assembly (CLR) DML trigger TT = TABLE type UQ = UNIQUE constraint (type: K) this table contains all objects in the database, such as the table Stored Procedure view and other information 2 object_id

All objects in the database are stored in the sysobjects system table. Each object is identified by a unique ID.

Object_id is the id of the object based on the object name.

2. Storage with Parameters

If (object_id ('test', 'P') is not null) drop proc testgocreate proc test (@ Id int) as select s_name store name, s_address store address, c_name salesperson from t_shop, t_cash_housewoker where s_id in (select s_id from manage where m_id = @ Id) and t_cash_housewoker.m_id = @ Idgo

Run:

exec test 1;

3. Storage with wildcards

if (object_id('test', 'P') is not null)    drop proc testgocreate proc test(@name varchar(20) = '%j%', @nextName varchar(20) = '%')as    select * from manage where m_name like @name or m_name like @nextName;go

Run:

Exec test; exec test '% step %', '% u % ';

4. Stored Procedures with output parameters

If (object_id ('test', 'P') is not null) drop proc testgocreate proc test (@ id int, -- default input parameter @ name varchar (20) out, -- output parameter @ age varchar (20) output -- input and output parameter) as select @ name = m_name, @ age = m_bir from manage where m_id = @ id

Run:

declare @id int,        @name varchar(20),        @bir varchar(20);set @id = 1; exec test @id, @name out, @bir output;select @name, @bir;print @name + '#' + @bir;