存储过程范例

CREATE PROCEDURE xp_insertFranchise 
@FranName nvarchar(50),
@FranCode varchar(10),
@EmplPrefix char(2),
@MaxCode char(6),
@IsOpen bit,
@ProcedureStatus char(2) out
AS
declare @IsFranExist int
declare @IsGroupAdminExist int
declare @IsGroupGuestExist int
declare @FranID uniqueidentifier
declare @DeptID uniqueidentifier
declare @EmplID uniqueidentifier
declare @EmplRecordID uniqueidentifier
declare @GroupAdminID int
declare @GroupGuestID int
declare @Error int
declare @@FranID uniqueidentifier


set @@FranID = "00000000-0000-0000-0000-000000000000"    --默认品牌编号
set @Error = 0

begin TRANSACTION

--获取品牌代码是否存在
set @IsFranExist = (select count(*) from Franchise where fran_code = @FranCode)
--获取群组权限
set @IsGroupAdminExist = (select XPS_GROUP_ID from xps_group where fran_id = @@FranID and XPS_GROUP_NAME = 'AdminTemplate')
set @IsGroupGuestExist = (select XPS_GROUP_ID from xps_group where fran_id = @@FranID and XPS_GROUP_NAME = 'GuestTemplate')


--找不到品牌代码则可以新增
if @IsFranExist =  0    
    begin
    --判断群组表是否有AdminTemplate和Guest两个权限,
    if @IsGroupAdminExist > 0 and @IsGroupGuestExist > 0 
        begin
            set @FranID= newid()                --品牌ID
            set @DeptID= newid()                --部门ID
            set @EmplID = newid()                --人员基本信息ID
            set @EmplRecordID = newid()            --人员任职ID

            --新增品牌资料
            Insert into Franchise(FRAN_ID,FRAN_CODE,FRAN_NAME,PRE,SUF,show_public) values(@FranID,@FranCode,@FranName,@EmplPrefix,@MaxCode,@IsOpen)
            set @Error = @@Error            

            --新增AdminTemplate群组
            Insert into xps_group(xps_group_name,fran_id,xps_admin,xps_guest) 
                select '管理员',@FranID,1,xps_guest from xps_group 
                    where fran_id = @@FranID and XPS_GROUP_NAME = 'AdminTemplate'
                     set @GroupAdminID = @@IDENTITY        --获取新品牌群组[AdminTemplate] 的ID
            set @Error = @Error + @@Error

            --新增Guest群组
            Insert into xps_group(xps_group_name,fran_id,xps_admin,xps_guest) 
                select 'Guest',@FranID,xps_admin,1 from xps_group 
                    where fran_id = @@FranID and XPS_GROUP_NAME = 'GuestTemplate'
            set @GroupGuestID = @@IDENTITY        --获取新品牌群组[Guest] 的ID
            set @Error = @Error + @@Error
            
            --新增AdminTemplate群组权限
            Insert into xps_group_list(xps_group_id,xps_menu_id,xps_menu_scope,xps_menu_consign)
                 select @GroupAdminID,xps_menu_id,xps_menu_scope,xps_menu_consign from xps_group_list 
                    where xps_group_id = @IsGroupAdminExist
            set @Error = @Error + @@Error

            --新增Guest群组权限
            Insert into xps_group_list(xps_group_id,xps_menu_id,xps_menu_scope,xps_menu_consign)
                 select @GroupGuestID,xps_menu_id,xps_menu_scope,xps_menu_consign from xps_group_list 
                    where xps_group_id = @IsGroupGuestExist
            set @Error = @Error + @@Error

            --新增部门
            Insert into dpt(row_id,dpt,dpt_group,name,city_code,startdate,status,parent_row_id,fran_id,Dpt_open_close)
                values(@DeptID,@FranCode,'xp',@FranName,'00',getDate(),1,@FranID,@FranID,1)
            set @Error = @Error + @@Error
            
            --新增人员基本信息
            Insert into u_emp(row_id,fran_id,emp_sid,emp_pwd,emp_name,emp_user,emp_brth,emp_code)
                values(@EmplID,@FranID,'12345678',@FranCode,'管理员','administrator','1900-1-1',@EmplPrefix+@MaxCode)
            set @Error = @Error + @@Error            

            --新增人员任职信息
            Insert into u_emp_record(id,emp_row_id,emp_dpt_id,emp_level,emp_dpt_status,emp_group_id)
                values(@EmplRecordID,@EmplID,@DeptID,1,1,@GroupAdminID)
            set @Error = @Error + @@Error            

            --新增AdminTemplate委托权
            Insert into xps_group_consign(xps_group_id,xps_consign_type,xps_group_user_id,xps_group_consign)
                values(@GroupAdminID,1,@GroupAdminID,1)
            set @Error = @Error + @@Error

            --新增Guest委托权
            Insert into xps_group_consign(xps_group_id,xps_consign_type,xps_group_user_id,xps_group_consign)
                values(@GroupGuestID,1,@GroupGuestID,1)
            set @Error = @Error + @@Error

            set @ProcedureStatus = "ok"            --状态成功
            
                       end
    else
        set @ProcedureStatus = 2        --不存在AdminTemplate及Guest权限
    end
else
    begin
    set @ProcedureStatus = 1            --品牌代码已经存在
    end






--事务出错则回滚事务至初始状态
if @Error <> 0

    begin
        Rollback  TRANSACTION
    end
else
    begin
        commit TRANSACTION
    end
GO




调用函数
  public static SqlParameter[] doInsertFran(string FranName,string FranCode,string EmplPrefix,string MaxCode,int IsOpen,string CityCode,string Area,int serviceUsing)
  {
   SqlParameter[] cmdParams = new SqlParameter[9];

   cmdParams[0] = new SqlParameter("@FranName",SqlDbType.NVarChar,50);
   cmdParams[0].Value = FranName;
   cmdParams[1] = new SqlParameter("@FranCode",SqlDbType.VarChar,10);
   cmdParams[1].Value = FranCode;
   cmdParams[2] = new SqlParameter("@EmplPrefix",SqlDbType.Char,2);
   cmdParams[2].Value = EmplPrefix;
   cmdParams[3] = new SqlParameter("@MaxCode",SqlDbType.Char,6);
   cmdParams[3].Value = MaxCode;
   cmdParams[4] = new SqlParameter("@IsOpen",SqlDbType.Bit,1);
   cmdParams[4].Value = IsOpen;
   cmdParams[5] = new SqlParameter("@CityCode",SqlDbType.VarChar,50);
   cmdParams[5].Value = CityCode;
   cmdParams[6] = new SqlParameter("@Area",SqlDbType.VarChar,50);
   cmdParams[6].Value = Area;
   cmdParams[7] = new SqlParameter("@serviceUsing",SqlDbType.Bit,1);
   cmdParams[7].Value = serviceUsing;
   cmdParams[8] = new SqlParameter("@ProcedureStatus",SqlDbType.Char,2);
   cmdParams[8].Direction = ParameterDirection.Output;

   XpData.SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"xp_insertFranchise",cmdParams);
   return cmdParams;
  }