34,575
社区成员
发帖
与我相关
我的任务
分享
/* 建立扩展存储过程 begin */
CREATE ASSEMBLY JadeFashionProcedure from 'F:\JadeFashionDB\JadeFashionProcedure.dll' WITH PERMISSION_SET = SAFE
Go
CREATE PROCEDURE GetUUID_PROC
@value nvarchar(64) output
AS
EXTERNAL NAME JadeFashionProcedure.JadeFashionProcedure.GetUUID
go
EXEC sp_configure 'clr enabled', '1';
RECONFIGURE;
--exec GetUUID --测试调用扩展存储过程
/* 建立扩展存储过程 end */
GO
/* BEGIN
新增权限的初始化
选择相应的权限组之后,自动生成所有模块的初始权限
*/
If exists(select 1 from sysobjects where name='IniInsert_T0015_PROC')
drop procedure IniInsert_T0015_PROC
go
CREATE PROCEDURE IniInsert_T0015_PROC
@GroupID varchar(50)
with encryption
AS
begin
DECLARE @MODULEID varchar(50)
DECLARE @UUID nvarchar(64)
DECLARE @SQL varchar(1000)
DECLARE t0001_cursor CURSOR FOR
SELECT F0001 FROM T0001 where F0005<>''
ORDER BY F0008
OPEN t0001_cursor
FETCH NEXT FROM t0001_cursor
INTO @MODULEID
WHILE @@FETCH_STATUS = 0
BEGIN
exec GetUUID_PROC @UUID output
set @SQL = 'insert into T0015(F0001,F0002,F0003,F0004) values('''+@UUID+''','''+@GroupID+''','''+@MODULEID+''',''00000'')'
select @SQL
if(not exists(select 1 from T0015 where F0002= @GroupID and F0003=@MODULEID))
begin
exec(@SQL)
end
FETCH NEXT FROM t0001_cursor
INTO @MODULEID
END
CLOSE t0001_cursor
DEALLOCATE t0001_cursor
end
go
--示例 exec IniInsert_T0015_PROC 'bd98b59926fb4a91a19dbc48ccbfe365'
--测试 select * from T0015 delete from T0015
/* END */
/* 建立取UUID的函数 */
GO
IF OBJECT_ID ('GetUUID', N'FN') IS NOT NULL
DROP FUNCTION GetUUID;
GO
CREATE FUNCTION GetUUID()
RETURNS nvarchar(64)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @UUID nvarchar(64)
exec GetUUID_PROC @UUID output
--select @UUID
return @UUID
END;
GO