一個預存程序完成插入,更新,刪除

來源:互聯網
上載者:User
插入|預存程序 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



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。