SqlServer存储过程

新上线的车管系统，今天要给User添加权限，才发现这个后台加权限简直how to play ..

比如有人申请合肥的关务权限:

1.SITE 是合肥

2.ORG 有B81，P81，S81，M81等

3.关务权限包括5个functionId

所以，总共要有20条记录

问题是，后台操作界面简直掉渣天，加一条权限，操作起码要有2~3分钟！！

只好想办法自己解决：

创建了一张配置表

写了一个存储过程

 1 USE [In_Out_Control]
 2 GO
 3 
 4 /****** Object:  StoredProcedure [dbo].[sp_AddAuthority]    Script Date: 11/04/2013 20:09:11 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 -- =============================================
12 -- Author: donghao.li
13 -- Create date: 2013-11-04
14 -- Description:    Authority
15 -- =============================================
16 CREATE PROCEDURE [dbo].[sp_AddAuthority]
17     -- Add the parameters for the stored procedure here
18     @empNo varchar(10),
19     @site varchar(10),
20     @authorityName varchar(50)
21 AS
22 BEGIN
23     SET NOCOUNT ON;
24     -- SET NOCOUNT ON added to prevent extra result sets from
25     -- interfering with SELECT statements.
26     declare @org varchar(20),@functionId varchar(50),@empName varchar(30)    
27     select  @empName=emp_name from emp_data_all where emp_no=@empNo
28     if (@empName !='')
29     BEGIN
30         declare my_cursor cursor for 
31         select org,functionId from C_IOC_USER_ABILITYCONTROL_TEMP where site=@site and authorityName=@authorityName order by org    
32         open my_cursor 
33         fetch my_cursor into @org,@functionId    
34         while (@@fetch_status=0)
35         BEGIN
36             declare @isExist int
37             select @isExist=count(*) from c_ioc_usersite where org=@org and function_id=@functionId and emp_no=@empNo
38                 -- Insert statements for procedure here
39             if (@isExist<=0)
40                 BEGIN
41                      insert into c_ioc_usersite(belong_company,emp_no,emp_name,company,site,org,active,function_id,create_date,create_by) values('BriView',@empNo,@empName,'briview',@site,@org,'Y',@functionId,getdate(),'C1226724') 
42                 END
43             fetch next from my_cursor into @org,@functionId
44         END
45         close my_cursor
46         deallocate my_cursor
47     END
48 end
49 GO

View Code

然后执行存储过程

 1 USE [In_Out_Control]
 2 GO
 3 
 4 DECLARE    @return_value int
 5 
 6 EXEC    @return_value = [dbo].[sp_AddAuthority]
 7         @empNo = N'C1226724',
 8         @site = N'BVHF',
 9         @authorityName = N'库房'
10 
11 SELECT    'Return Value' = @return_value
12 
13 GO

View Code

好吧，这样方便快速了很多~