一个存储过程完成插入，更新，删除-ASP教程,数据库相关-数据库其他-idc中文资讯站-客观公证的IDC产业权威媒体,关注域名、虚拟主机、服务器、网络、开源技术资讯，整合运营商资源，客观评价各地机房带宽线路质量、IDC企业名录等信息

一个存储过程完成插入，更新，删除-ASP教程,数据库相关

来源：作者：发布时间:2007-12-26 02:11:48

1.数据表
create table [nta_our_travelco] (
[travelco_id] [int] identity (1, 1) not for replication not null ,
[travelco_name] [varchar] (80) collate chinese_prc_ci_as null ,
[travelco_linkman] [varchar] (20) collate chinese_prc_ci_as null ,
[travelco_phone] [varchar] (20) collate chinese_prc_ci_as null ,
[travelco_mobile] [varchar] (20) collate chinese_prc_ci_as null ,
[travelco_check] [char] (1) collate chinese_prc_ci_as null ,
[travelco_website] [varchar] (80) collate chinese_prc_ci_as null ,
[travelco_email] [varchar] (50) collate chinese_prc_ci_as null ,
[travelco_fax] [char] (20) collate chinese_prc_ci_as null ,
[travelco_address] [varchar] (200) collate chinese_prc_ci_as null ,
[travelco_intro] [varchar] (1000) collate chinese_prc_ci_as null ,
[travelco_tlicenseno] [varchar] (30) collate chinese_prc_ci_as null constraint

[df_nta_our_travelco_travelco_tlicenseno] default (交观甲号),
[travelco_qlicenseno] [varchar] (30) collate chinese_prc_ci_as null constraint

[df_nta_our_travelco_travelco_qlicenseno] default (品保(北)字号),
[travelco_ename] [varchar] (80) collate chinese_prc_ci_as null ,
[travelco_licenseno] [varchar] (10) collate chinese_prc_ci_as null ,
[travelco_agentceo] [varchar] (20) collate chinese_prc_ci_as null ,
[username] [varchar] (20) collate chinese_prc_ci_as null ,
[password] [varchar] (32) collate chinese_prc_ci_as null ,
constraint [pk_nta_our_travelco] primary key clustered
(
[travelco_id]
) on [primary]
) on [primary]
go

2.存储过程
set quoted_identifier off
go
set ansi_nulls off
go

alter procedure nta_our_travelco_update
@action int,
@travelco_id int =null,
@travelco_name varchar(80) =null,
@travelco_linkman varchar(20) =null,
@travelco_phone varchar(20) =null,
@travelco_mobile varchar(20) =null,
@travelco_website varchar(80) =null,
@travelco_email varchar(50) =null,
@travelco_fax char(20) =null,
@travelco_address varchar(200) =null,
@travelco_intro varchar(500) =null,
@travelco_tlicenseno varchar(30) =null,
@travelco_qlicenseno varchar(30) =null,
@travelco_ename varchar(80) =null,
@travelco_licenseno varchar(10) =null,
@travelco_agentceo varchar(20) =null,
@username varchar(20)=null,
@password varchar(32)=null,
@sessionid int=null, --管理员session
@password_md5 varchar(32)
as

if @action=1 ---同行注册
begin
if exists(select * from nta_our_travelco where username = @username)
select 10 --用户存在
else --用户不存在，快速注册
begin
insert into nta_our_travelco(
travelco_name ,
travelco_linkman ,
travelco_phone ,
travelco_mobile ,
travelco_website ,
travelco_email ,
travelco_fax ,
travelco_address ,
travelco_intro ,
travelco_tlicenseno ,
travelco_qlicenseno ,
travelco_ename ,
travelco_licenseno ,
travelco_agentceo,username,password
)
values(
@travelco_name ,
@travelco_linkman ,
@travelco_phone ,
@travelco_mobile ,
@travelco_website ,
@travelco_email ,
@travelco_fax ,
@travelco_address ,
@travelco_intro ,
@travelco_tlicenseno ,
@travelco_qlicenseno ,
@travelco_ename ,
@travelco_licenseno ,
@travelco_agentceo,@username,@password_md5
)
select 1 --注册成功
end
end

if @action=2 --同行修改注册信息
update nta_our_travelco set
travelco_id = @travelco_id ,
travelco_name = @travelco_name ,
travelco_linkman = @travelco_linkman ,
travelco_phone = @travelco_phone ,
travelco_mobile = @travelco_mobile ,
travelco_website = @travelco_website ,
travelco_email = @travelco_email ,
travelco_fax = @travelco_fax ,
travelco_address = @travelco_address ,
travelco_intro = @travelco_intro ,
travelco_tlicenseno = @travelco_tlicenseno ,
travelco_qlicenseno = @travelco_qlicenseno ,
travelco_ename = @travelco_ename ,
travelco_licenseno = @travelco_licenseno ,
travelco_agentceo = @travelco_agentceo
where
travelco_id = @travelco_id
select 2 --更新成功

if @action=3 --审核同行
if @sessionid=null
begin
select 5 --不是管理员id，无法审核同行
end
else
begin
update nta_our_travelco set
travelco_check = 1
where
travelco_id = @travelco_id
select 3 --审核通过
end

if @action=4 --取消同行
if @sessionid=null
begin
select 6 --不是管理员id，无法取消同行
end
else
begin
update nta_our_travelco set
travelco_check = 0
where
travelco_id = @travelco_id
select 4 --取消成功
end

if @action=5 --同行登录
begin
if not exists(select * from nta_our_travelco where username = @username)
select 11 --用户不存在
else
begin
declare @pwd char(32)
select @pwd=password from nta_our_travelco where username = @username
if @pwd=@password_md5
select 12 --登录成功
else
begin
select 13 --密码错误
--select @userid = -1
end

end
end

if @action=6 --同行更改密码
update nta_our_travelco set
password = @password_md5
where
travelco_id = @travelco_id
select 14 --更改密码成功
go
set quoted_identifier off
go
set ansi_nulls on
go

3.asp调用
<%
省略数据库调用
action,travelco_id,travelco_name等等的参数自己定义！

set rs=server.createobject ("adodb.recordset")
set cm=server.createobject("adodb.command")
cm.parameters(1) = action
cm.parameters(2) = travelco_id
cm.parameters(3) = travelco_name
cm.parameters(4) = travelco_linkman
cm.parameters(5) = travelco_phone
cm.parameters(6) = travelco_mobile
cm.parameters(7) = travelco_check
cm.parameters(8) = travelco_website
cm.parameters(9) = travelco_email
cm.parameters(10) = travelco_fax
cm.parameters(11) = travelco_address
cm.parameters(12) = travelco_intro
cm.parameters(13) = travelco_tlicenseno
cm.parameters(14) = travelco_qlicenseno
cm.parameters(15) = travelco_ename
cm.parameters(16) = travelco_licenseno
cm.parameters(17) = travelco_agentceo
cm.parameters(18) = username
cm.parameters(19) = password
cm.parameters(20) = sessionid
cm.parameters(21) = password_md5
rs.cursorlocation=3
rs.locktype=3
rs.open cm

if rs(0)=1 then
str="注册成功"

elseif rs(0)=2 then
str="更新成功"

elseif rs(0)=3 then
str="审核通过"

elseif rs(0)=4 then
str="取消成功"

elseif rs(0)=5 then
str="不是管理员id，无法审核同行"

elseif rs(0)=6 then
str="不是管理员id，无法取消同行"

elseif rs(0)=10 then
str="用户存在"

elseif rs(0)=11 then
str="不是管理员id，无法取消同行"

elseif rs(0)=12 then
str="登录成功"

elseif rs(0)=13 then
str="密码错误"

elseif rs(0)=14 then
str="更改密码成功"

end if

response.write str
response.end
%>

◇ 广告时间：
数据库代码，文档由86fifa编写整理。
灌水公司提供专业级的网站程式研发，数据库研发，收费技术支持、安全顾问服务

联系方式：
qq组：1019634 （nb联盟）

喜欢本文，那就收藏到：