USE [In_Out_Control]

 GO

 /****** Object:  StoredProcedure [dbo].[sp_AddAuthority]    Script Date: 11/04/2013 20:09:11 ******/

 SET ANSI_NULLS ON

 GO

 SET QUOTED_IDENTIFIER ON

 GO

 -- =============================================

 -- Author: donghao.li

 -- Create date: 2013-11-04

 -- Description:    Authority

 -- =============================================

 CREATE PROCEDURE [dbo].[sp_AddAuthority]

     -- Add the parameters for the stored procedure here

     @empNo varchar(10),

     @site varchar(10),

     @authorityName varchar(50)

 AS

 BEGIN

     SET NOCOUNT ON;

     -- SET NOCOUNT ON added to prevent extra result sets from

     -- interfering with SELECT statements.

     declare @org varchar(20),@functionId varchar(50),@empName varchar(30)

     select  @empName=emp_name from emp_data_all where emp_no=@empNo

     if (@empName !='')

     BEGIN

         declare my_cursor cursor for

         select org,functionId from C_IOC_USER_ABILITYCONTROL_TEMP where site=@site and authorityName=@authorityName order by org

         open my_cursor

         fetch my_cursor into @org,@functionId

         while (@@fetch_status=0)

         BEGIN

             declare @isExist int

             select @isExist=count(*) from c_ioc_usersite where org=@org and function_id=@functionId and emp_no=@empNo

                 -- Insert statements for procedure here

             if (@isExist<=0)

                 BEGIN

                      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')

                 END

             fetch next from my_cursor into @org,@functionId

         END

         close my_cursor

         deallocate my_cursor

     END

 end

 GO

 USE [In_Out_Control]

 GO

 DECLARE    @return_value int

 EXEC    @return_value = [dbo].[sp_AddAuthority]

         @empNo = N'C1226724',

         @site = N'BVHF',

         @authorityName = N'库房'

 SELECT    'Return Value' = @return_value

 GO