原文:
SQL點滴28—一個簡單的存儲過程
在表中寫入一條數據同事要向另外一個表中寫入信息，所以會使用到事務。實際使用的時候還會一次向一個表中吸入多條數據，下面的存儲過程，將字符串拆分成數組然后寫入到表中。
/*
***** Object: StoredProcedure [dbo].[sp_InsertEmployee] Script Date: 09/17/2012 23:28:42 *****
*/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
--
=============================================
--
Author: <Author,,Name>
--
Create date: <Create Date,,>
--
Description: 插入一條雇員數據
--
=============================================
CREATE
PROCEDURE
[
dbo
]
.
[
sp_InsertEmployee
]
--
Add the parameters for the stored procedure here
@Name
varchar
(
50
),
@UserName
varchar
(
50
),
@Password
varchar
(
50
),
@Hierarchy
char
(
1
),
@EmployeeTypeID
int
,
@Sex
varchar
(
5
),
@Telphone
varchar
(
20
),
@CellPhone
varchar
(
20
),
@QQ
varchar
(
20
),
@Email
varchar
(
50
),
@Statue
varchar
(
20
),
@Remark
varchar
(
50
),
@ManagerID
int
,
@Regions
varchar
(
1000
)
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT
ON
;
declare
@ID
int
declare
@tempSql
varchar
(
2000
)
--
Insert statements for procedure here
if
exists
(
select
*
from
Employee
where
Name
=
@Name
)
return
0
begin
transaction
insert
into
[
Employee
]
(
[
Name
]
,
[
UserName
]
,
[
Password
]
,
[
Hierarchy
]
,
[
EmployeeTypeID
]
,
[
Sex
]
,
[
Telphone
]
,
[
CellPhone
]
,
[
QQ
]
,
[
Email
]
,
[
Statue
]
,
[
Remark
]
,
[
ManagerID
]
)
values
(
@Name
,
@UserName
,
@Password
,
@Hierarchy
,
@EmployeeTypeID
,
@Sex
,
@Telphone
,
@CellPhone
,
@QQ
,
@Email
,
@Statue
,
@Remark
,
@ManagerID
)
set
@ID
=
@@IDENTITY
if
exists
(
select
*
from
Permission
where
ID
=
@ID
)
begin
delete
from
Permission
where
EmployeeID
=
@ID
end
else
begin
set
@tempSql
=
'
insert into Permission select
'
+
str
(
@ID
)
+
'
,
'''
+
replace
(
@Regions
,
'
,
'
,
'''
union select
'
+
str
(
@ID
)
+
'
,
'''
)
+
''''
exec
(
@tempSql
)
end
if
@@ERROR
>
0
begin
rollback
transaction
end
else
begin
commit
transaction
end
END
GO
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
