原文:
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元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
