USE [XXXX]
GO

The SET ANSI_NULLS ON
GO
The SET QUOTED_IDENTIFIER ON
GO
The ALTER proc [dbo] [P_Sys_GetModule_RoleRight]
@ moduleId varchar (36)
As
/*

*/

The create table # userModule
(
Id varchar (36),
The name varchar (1024),
KeyCode varchar (1024),
IsValid bit
)

- declare @ moduleId varchar (36)
- set @ moduleId='000001'

Select a. *, b.N ame, c.K eyCode, c.I sValid into # right
A, the from SysRight SysRole b, SysRightOperate c where arjun oduleId=@ moduleId
And a.R oleId=b.I d
And Anderson, d=c.R ightId

- select * from # right
Select distinct Id, ModuleId, RoleId, Rightflag, Name
Into # right2
The from # right
- select * from # right2

Select a. *, b.N ame as keycode, b.I sValid
Into # right3
The from # right2 a, SysModuleOperate b
Where arjun oduleId=b.M oduleId
And RoleId + keycode not in (
The select RoleId + KeyCode from # right
)
Insert into # right
Select * from # right3
Where RoleId + keycode not in (
The select RoleId + KeyCode from # right
)

Update # right set keyCode=b.N ame from # right. A, b SysModuleOperate where arjun oduleId=b.M oduleId
And a.K eyCode=b.K eyCode

- select * from # right
Insert into # userModule (Id, name, keyCode, isValid)
The select RoleId, Name, KeyCode, IsValid from # right

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- select * from # userModule

Declare @ SQL varchar (1024)

The create table # moduleKeyCode
(
Id varchar (36),
The Name varchar (1024),
KeyCode varchar (1024)
)
DECLARE @ id varchar (128), @ name varchar (128)
DECLARE @ moduleKeyCodeIsTrue varchar (1024), @ moduleKeyCodeIsFalse varchar (1024), @ moduleKeyCode varchar (1024)
DECLARE @ keyCode varchar (256), @ isValid bit

DECLARE roleModule_cursor CURSOR FOR
SELECT distinct Id, name from # userModule

The OPEN roleModule_cursor

The FETCH NEXT FROM roleModule_cursor
INTO @ id, @ name

WHILE @ @ FETCH_STATUS=0
The BEGIN
The set @ moduleKeyCodeIsTrue='
The set @ moduleKeyCodeIsFalse='

- start reading KeyCode
DECLARE keyCode_cursor CURSOR FOR
SELECT the keycode, isvalid from # userModule where Id=@ Id

The OPEN keyCode_cursor

The FETCH NEXT FROM keyCode_cursor
INTO @ keyCode, @ isValid

WHILE @ @ FETCH_STATUS=0
The BEGIN
If (@ isValid=1)
The begin
The set @ moduleKeyCodeIsTrue=@ moduleKeyCodeIsTrue + '& lt; A/& gt; '+ @ keyCode
End
The else
The begin
The set @ moduleKeyCodeIsFalse=@ moduleKeyCodeIsFalse + '& lt; A/& gt; '+ @ keyCode
End

The FETCH NEXT FROM keyCode_cursor
INTO @ keyCode, @ isValid
END
The CLOSE keyCode_cursor;
DEALLOCATE keyCode_cursor;
-//
If (@ moduleKeyCodeIsTrue!=' ')
The begin
IF (@ moduleKeyCodeIsFalse!=' ')
The BEGIN
SET @ moduleKeyCode="' + @ moduleKeyCodeIsTrue +" + @ moduleKeyCodeIsFalse
END
The ELSE
The BEGIN
SET @ moduleKeyCode="' + @ moduleKeyCodeIsTrue
END
Insert into # moduleKeyCode
Values (@ id, @ name, @ moduleKeyCode)
End

The FETCH NEXT FROM roleModule_cursor
INTO @ id, @ name
END
The CLOSE roleModule_cursor;
DEALLOCATE roleModule_cursor;

Select Id, Name, keyCode from # moduleKeyCode

Drop table # moduleKeyCode
Drop table # userModule
Drop table # right

The above is the SQL stored procedure hope to be able to transfer into MYSQL