一个例子:drop table new_tree go /*parent对应父亲结点,child对应儿子结点,如果child is NULL则本结点为叶子结点*/ create table new_tree ( parent varchar(80), child varchar(80)) go insert new_tree values ( '1','2'); insert new_tree values ( '1','3'); insert new_tree values ( '2','4'); insert new_tree values ( '2','5'); insert new_tree values ( '3','6'); insert new_tree values ( '3','7'); insert new_tree values ( '3','8'); insert new_tree values ( '6','9'); insert new_tree values ( '5','10'); insert new_tree values ( '4','11'); insert new_tree values ( '9','12'); insert new_tree values ( '7',NULL); insert new_tree values ( '8',NULL); insert new_tree values ( '10',NULL); insert new_tree values ( '11',NULL); insert new_tree values ( '12',NULL);drop proc proc_new_tree go /*@parent 输入根结点标识,@mode为0 则输出为所有子孙记录,否则输出所有叶子结点*/ create proc proc_new_tree (@parent varchar(80),@mode int =0) asbegin set nocount on /*如果不是SQLSERVER2000可以用临时表*/ declare @tmp1 table ( parent varchar(80), child varchar(80)) declare @tmp2 table ( parent varchar(80), child varchar(80)) declare @tmp3 table ( parent varchar(80), child varchar(80)) insert @tmp1 select * from new_tree where parent = @parent insert @tmp3 select * from new_tree where parent = @parent /*循环的次数等于树的深度*/ while exists(select * from @tmp1 where child is not NULL) begin insert @tmp2 select a.* from new_tree a,@tmp1 b where a.parent = b.child /*@tmp2表中存本次查询的层次的所有结点*/ delete from @tmp1 where child is not NULL /*@tmp1表中最终存的是叶子结点*/ insert @tmp1 select * from @tmp2 /*@tmp3表中最保存每次查到的子孙*/ insert @tmp3 select * from @tmp2 delete from @tmp2 end if @mode =0 select * from @tmp3 else select * from @tmp1 set nocount off end goproc_new_tree '1',1 go
高手就是不一样,给点简单点就可以了吧 语法 CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 建议你看联机文件 A. 使用带有复杂 SELECT 语句的简单过程 下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GOau_info_all 存储过程可以通过以下方法执行:EXECUTE au_info_all -- Or EXEC au_info_all如果该过程是批处理中的第一条语句,则可使用:au_info_allB. 使用带有参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P') DROP PROCEDURE au_info GO USE pubs GO CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GOau_info 存储过程可以通过以下方法执行:EXECUTE au_info 'Dull', 'Ann' -- Or EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' -- Or EXEC au_info 'Dull', 'Ann' -- Or EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXEC au_info @firstname = 'Ann', @lastname = 'Dull'如果该过程是批处理中的第一条语句,则可使用:au_info 'Dull', 'Ann' -- Or au_info @lastname = 'Dull', @firstname = 'Ann' -- Or au_info @firstname = 'Ann', @lastname = 'Dull'C. 使用带有通配符参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则使用预设的默认值。USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GOau_info2 存储过程可以用多种组合执行。下面只列出了部分组合:EXECUTE au_info2 -- Or EXECUTE au_info2 'Wh%' -- Or EXECUTE au_info2 @firstname = 'A%' -- Or EXECUTE au_info2 '[CK]ars[OE]n' -- Or EXECUTE au_info2 'Hunter', 'Sheryl' -- Or EXECUTE au_info2 'H%', 'S%'[ FOR REPLICATION ] AS sql_statement [ ...n ]
go
/*parent对应父亲结点,child对应儿子结点,如果child is NULL则本结点为叶子结点*/
create table new_tree ( parent varchar(80), child varchar(80))
go
insert new_tree values ( '1','2');
insert new_tree values ( '1','3');
insert new_tree values ( '2','4');
insert new_tree values ( '2','5');
insert new_tree values ( '3','6');
insert new_tree values ( '3','7');
insert new_tree values ( '3','8');
insert new_tree values ( '6','9');
insert new_tree values ( '5','10');
insert new_tree values ( '4','11');
insert new_tree values ( '9','12');
insert new_tree values ( '7',NULL);
insert new_tree values ( '8',NULL);
insert new_tree values ( '10',NULL);
insert new_tree values ( '11',NULL);
insert new_tree values ( '12',NULL);drop proc proc_new_tree
go
/*@parent 输入根结点标识,@mode为0 则输出为所有子孙记录,否则输出所有叶子结点*/
create proc proc_new_tree (@parent varchar(80),@mode int =0)
asbegin
set nocount on
/*如果不是SQLSERVER2000可以用临时表*/
declare @tmp1 table ( parent varchar(80), child varchar(80))
declare @tmp2 table ( parent varchar(80), child varchar(80))
declare @tmp3 table ( parent varchar(80), child varchar(80)) insert @tmp1 select * from new_tree where parent = @parent
insert @tmp3 select * from new_tree where parent = @parent /*循环的次数等于树的深度*/
while exists(select * from @tmp1 where child is not NULL)
begin
insert @tmp2 select a.* from new_tree a,@tmp1 b where a.parent = b.child
/*@tmp2表中存本次查询的层次的所有结点*/
delete from @tmp1 where child is not NULL
/*@tmp1表中最终存的是叶子结点*/
insert @tmp1 select * from @tmp2
/*@tmp3表中最保存每次查到的子孙*/
insert @tmp3 select * from @tmp2
delete from @tmp2
end
if @mode =0 select * from @tmp3
else select * from @tmp1
set nocount off
end
goproc_new_tree '1',1
go
语法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 建议你看联机文件
A. 使用带有复杂 SELECT 语句的简单过程
下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GOau_info_all 存储过程可以通过以下方法执行:EXECUTE au_info_all
-- Or
EXEC au_info_all如果该过程是批处理中的第一条语句,则可使用:au_info_allB. 使用带有参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GOau_info 存储过程可以通过以下方法执行:EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'如果该过程是批处理中的第一条语句,则可使用:au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'C. 使用带有通配符参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则使用预设的默认值。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GOau_info2 存储过程可以用多种组合执行。下面只列出了部分组合:EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'[ FOR REPLICATION ] AS sql_statement [ ...n ]
另外,在存储过程中比较好的一点是可以整体调用和能够灵活的输入参数,你可以先参看sql中系统自带的例子,再从简单的比如查询开始,学写,在理解格式,它的作用之后,再加入一些复杂一点的东西,你就会深刻的理解的
在需要调用的地方,比如触发器,程序中,exec一下,就行了.是快速执行SQL语句的命名和编译的集合
@参数 类型
as
语句如:
create procedure MyProcedure
@AAA int
as
if @AAA=1
select '大家好'
else
select * from pubs..jsbs
selet * (可以选择记录名称)
from 表1(可以选择要选择的表单的名称)
where recordname(查询字段)
ADO就主要是那么一点点东西,自己努力一下,看一点电子书什么的就可以了!