帮看下这个存储过程怎样优化。
ALTER PROCEDURE [dbo].[Mcd_cr_user_AddNewUser] @userId int,@nowSystem_id int,@OpUserId intASBEGIN--从临时表读取数据存入临时表select [region_id],[first_name],[second_name],[update_user],[update_time],[ad_account],[email] into #T from [MCDTemp].[dbo].[McD_CR_Tmp_User] where [tmp_user_id]=@userId--定义存入实际用户表的变量名declare @RegionId intdeclare @firstname varchar(50)declare @secondname varchar(50)declare @password varchar(50)declare @updatetime smalldatetimedeclare @updateruser intdeclare @ad_account nvarchar(50)declare @email nvarchar(100)declare @expire_flag bitdeclare @delete_flag bitdeclare @newUserId intdeclare @isAdmin int--从临时表中读取数据给变量赋值select @RegionId= [region_id] from #T select @firstname=[first_name] from #Tselect @secondname=[second_name] from #Tselect @updatetime=[update_time] from #Tselect @updateruser=[update_user] from #Tselect @ad_account=[ad_account] from #Tselect @email=[email] from #Tset @expire_flag= 1set @delete_flag= 0set @password= 'pass1234'set @isAdmin= 0--执行插入新表操作insert into [McD_CR].[dbo].[McD_Sys_Data_User]([region_id],[first_name],[second_name],[update_user],[update_time],[ad_account],[email],[expire_flag],[delete_flag],[password],[is_admin])values(@RegionId,@firstname,@secondname,@updateruser,@updatetime,@ad_account,@email,@expire_flag,@delete_flag,@password,@isAdmin)--取得新增的userIDselect @newUserId=(select [USER_ID] from [McD_CR].[dbo].[McD_Sys_Data_User] where [region_id]=@RegionId and [first_name]=@firstname and [second_name]=@secondname)--定义存入新角色表关系游标中的变量declare @role_rel_role_id intdeclare @role_rel_module_id int--定义存入新角色关系表的变量declare @newRole_userId intdeclare @newRole_relId intdeclare @newRole_moduleRel_id int--读取临时表该用户的角色关系记录存入新角色关系表declare MyCursorRoleRel cursor for select [role_id],[module_id] FROM [MCDTemp].[dbo].[McD_CR_Tmp_User_Role] where [tmp_user_id]=@userIdopen MyCursorRoleRelfetch next from MyCursorRoleRel into @role_rel_role_id,@role_rel_module_idwhile(@@fetch_status=0) begin select @newRole_userId= @newUserId select @newRole_relId= @role_rel_role_id select @newRole_moduleRel_id= @role_rel_module_id insert into [McD_CR].[dbo].[McD_Sys_Rel_User_Role] values(@newRole_userId,@newRole_relId,@newRole_moduleRel_id) fetch next from MyCursorRoleRel into @role_rel_role_id,@role_rel_module_id end close MyCursorRoleRel deallocate MyCursorRoleRel --新增用户角色关系表添加结束--定义存入新用户组织关系游标中的变量declare @orgrel_orgId int--定义存入新用户组织关系表中的变量declare @newOrgRel_userId intdeclare @newOrgRel_orgId intdeclare @system_id intdeclare @update_id intdeclare @update_time smalldatetime--读取临时表中的数据存入新用户组织关系表declare MyCursorRoleOrg cursor for select [node_key] FROM [MCDTemp].[dbo].[McD_CR_Tmp_User_Org] where [tmp_user_id]=@userId and [org_type]=1open MyCursorRoleOrgfetch next from MyCursorRoleOrg into @orgrel_orgIdwhile(@@fetch_status=0) begin select @newOrgRel_orgId= @orgrel_orgId select @newOrgRel_userId= @newUserId select @system_id= @nowSystem_id select @update_id= @OpUserId select @update_time=GETDATE() insert into [McD_CR].[dbo].[McD_Sys_Rel_User_Org] values(@newOrgRel_userId,@newOrgRel_orgId,@system_id,@update_time,@update_id) fetch next from MyCursorRoleOrg into @orgrel_orgId end close MyCursorRoleOrg deallocate MyCursorRoleOrg--新增用户组织关系表添加结束--定义插入新组织关系授权表的变量declare @newOPSUserId intdeclare @newOPSNodeId intdeclare @newOPSNodeLevel intdeclare @newOpsUpdateTime smalldatetimedeclare @newOPSUpdateUserId intselect [node_key],[node_level] into #T1 FROM [MCDTemp].[dbo].[Mcd_CR_Tmp_User_Org] where [tmp_user_id]=@userId and [org_type]=2select @newOPSUserId= @newUserIdselect @newOPSNodeId=[node_key] from #T1select @newOPSNodeLevel=[node_level] from #T1set @newOpsUpdateTime=GETDATE()select @newOPSUpdateUserId=@OpUserIdinsert into [McD_CR].[dbo].[Mcd_Sys_Rel_User_Ops]values(@newOPSUserId,@newOPSNodeLevel,@newOPSNodeId,@newOpsUpdateTime,@newOPSUpdateUserId)--下面删除临时表中相关数据 DELETE FROM [MCDTemp].[dbo].[McD_CR_Tmp_User] where [tmp_user_id]=@userId DELETE FROM [MCDTemp].[dbo].[McD_CR_Tmp_User_Role] where [tmp_user_id]=@userId DELETE FROM [MCDTemp].[dbo].[McD_CR_Tmp_User_Org] where [tmp_user_id]=@userId drop table #T drop table #T1--下面添加对过期数据的处理--定义一个临时变量存储过期的userIddeclare @OldUserId intdeclare MyCursorDateCalc cursor for select [tmp_user_id] from [MCDTemp].[dbo].[McD_CR_Tmp_User]where datediff(d,[update_time],getdate())>1open MyCursorDateCalcfetch next from MyCursorDateCalc into @OldUserIdwhile(@@fetch_status=0) begin DELETE FROM [MCDTemp].[dbo].[McD_CR_Tmp_User] where [tmp_user_id]=@OldUserId DELETE FROM [MCDTemp].[dbo].[McD_CR_Tmp_User_Role] where [tmp_user_id]=@OldUserId DELETE FROM [MCDTemp].[dbo].[McD_CR_Tmp_User_Org] where [tmp_user_id]=@OldUserId fetch next from MyCursorDateCalc into @OldUserId end close MyCursorDateCalc deallocate MyCursorDateCalc END
