SQL Procedure示例程序03

最新推荐文章于 2025-12-20 22:59:04 发布

原创最新推荐文章于 2025-12-20 22:59:04 发布 · 292 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #delete

SQL SERVER 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种通过SQL存储过程实现批量为客户提供区域权限分配的方法，包括获取客户所属区域、查找具有相应区域权限的用户并为其分配权限的过程。

ALTER PROCEDURE [dbo].[AssignAreaRightToUsers] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @CustomerID                     varchar(50)

	DECLARE CustCurosr CURSOR FOR
		SELECT OID FROM tbl_customdata WHERE newflag=0

	OPEN CustCurosr
	FETCH NEXT FROM CustCurosr
		INTO @CustomerID
	WHILE @@FETCH_STATUS = 0
	BEGIN		
		exec [AssignAreaRightToUsersByOID] @CustomerID
		FETCH NEXT FROM CustCurosr
				INTO @CustomerID
	END	

	CLOSE CustCurosr
	DEALLOCATE CustCurosr	

END

ALTER PROCEDURE [dbo].[AssignAreaRightToUsersByOID] 
	@OID                    varchar(40)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @AreaID                        varchar(50)
	DECLARE @RAOID                         varchar(50)
	DECLARE @RIGHTEEOID                    varchar(50)   --用户ID

	--获取客户所属区域
	select @AreaID=tbl_saleorg_oid from tbl_customdata where oid =@OID
	PRINT '@AreaID=' + @AreaID

	--根据区域找到有该区域权限的的用户
	DECLARE MyCurosr CURSOR FOR
	SELECT DISTINCT OID,RIGHTEEOID FROM TBL_RIGHTACTION 
	WHERE OID
		IN(
			SELECT a.RAOID FROM TBLAREARIGHTS A, TBL_SALEORG B WHERE A.TBL_SALEORG_OID = B.OID 
			AND b.oid =@AreaID
			AND B.SOG_STATUS = 'USAGE' 
		)

	OPEN MyCurosr
	FETCH NEXT FROM MyCurosr
		INTO @RAOID,@RIGHTEEOID
	--给这些用户赋拥有这个客户的权限
	BEGIN TRY	
		BEGIN TRANSACTION 
		
		--先删除该客户对应的区域权限
		DELETE FROM TBLAREARIGHTS WHERE TBL_SALEORG_OID=lower(@OID) 

		WHILE @@FETCH_STATUS = 0
		BEGIN		
			PRINT '@RAOID=' + @RAOID
			PRINT '@RIGHTEEOID=' + @RIGHTEEOID		
			
			INSERT INTO TBLAREARIGHTS (TBL_SALEORG_OID,RAOID,ARS_RIGHTS) VALUES(lower(@OID),@RAOID,'Browse| | |')

			FETCH NEXT FROM MyCurosr
					INTO @RAOID,@RIGHTEEOID
		END
		UPDATE tbl_customdata SET newflag=1 WHERE oid = @OID		
		COMMIT TRANSACTION 
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION 	
	END CATCH

	CLOSE MyCurosr
	DEALLOCATE MyCurosr	

END