SQL点滴28—一个简单的存储过程
在表中写入一条数据同事要向另外一个表中写入信息，所以会使用到事务。实际使用的时候还会一次向一个表中吸入多条数据，下面的存储过程，将字符串拆分成数组然后写入到表中。
/****** Object: StoredProcedure [dbo].[sp_InsertEmployee] Script Date: 09/17/2012 23:28:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @ID intdeclare @tempSql varchar(2000)-- Insert statements for procedure hereif exists(select * from Employee where Name=@Name) return 0begin 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 endelse begin commit transaction end ENDGO
TAG:
