if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#table') and type='U')
DROP TABLE #table
CREATE TABLE #table
(
[RECNUM] [decimal](28, 0) IDENTITY(1, 1) NOT NULL ,
ModuleCode NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
FunctionCode NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
FuncDescription NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Process_Code NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Menu_Type NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #table(ModuleCode, FunctionCode , FuncDescription,Process_Code,Menu_Type)
SELECT 'PRSF', 'PRSFRT' , 'Matrial Reservation Transfer','M6_ENT','ENTRY'
UNION ALL
SELECT 'PRSF', 'PRSFRC' ,'Routing Process Completion','M6_ENT','ENTRY'
UNION ALL
SELECT 'PRPE', 'PRPEMJ' ,'Mass ECN','M4_ENT','ENTRY'
UNION ALL
SELECT 'PRPM', 'PRPMMJ' ,'Mass ECN','M4_ENT','ENTRY'
UNION ALL
SELECT 'SAMF', 'SAMFRP' ,'Routing Process','MAST01','MAST'
UNION ALL
SELECT 'SAMF', 'SAMFRP' ,'Bom Routing Process Settings','MAST01','MAST'
UNION ALL
SELECT 'SAMF', 'SAMFCL' ,'Collection Master','MAST01','MAST'
UNION ALL
SELECT 'SAMF', 'SAMFSY' ,'Style Master','MAST01','MAST'
UNION ALL
SELECT 'SAMF','SAMFCO' ,'Color Master','MAST01','MAST'
UNION ALL
SELECT 'PRPM', 'PRMJU' , 'Job BOM Update','M4_ENT','ENTRY'
Declare @RECNUM int
Declare @ModuleCode Nvarchar(30)
Declare @FunctionCode Nvarchar(30)
Declare @Process_Code Nvarchar(30)
Declare @Menu_Type Nvarchar(30)
Declare Cur Cursor For Select [RECNUM] From #table
Open Cur
Fetch next From Cur Into @RECNUM
While @@fetch_status=0
BEGIN
SELECT @ModuleCode=ModuleCode,@FunctionCode=FunctionCode,@Process_Code=Process_Code,@Menu_Type=Menu_Type
FROM #table WHERE RECNUM=@RECNUM
--[USER_GROUP], [PROCESS_CODE], [MENU_TYPE], [MENU_CODE]
DELETE ADMNUD WHERE USER_GROUP='SYSADM' AND PROCESS_CODE=@Process_Code AND MENU_TYPE=@Menu_Type AND FUNCTION_CODE=@FunctionCode
--([USER_GROUP], [MODULE_CODE], [FUNCTION_NO])
DELETE ADAUTD WHERE USER_GROUP='SYSADM' AND MODULE_CODE=@ModuleCode AND FUNCTION_CODE=@FunctionCode
--[MODULE_CODE], [FUNCTION_NO]
DELETE ADFUNC WHERE MODULE_CODE=@ModuleCode AND FUNCTION_CODE=@FunctionCode
Fetch next From Cur Into @RECNUM
End
Close Cur
Deallocate Cur