原文:
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
