将存储过程批量进行加密的语句(二)
new_definition
BEGIN TRY
----保证语句执行错误后，可以保留原不能替换语句，加事务
BEGIN TRAN new_definition
IF @new_definition IS NOT NULL
BEGIN
EXEC (' drop proc [' + @proc_name + ']' )--先DROP
EXEC (@new_definition) --再CREATE
UPDATE tempdb.dbo.wfp_before_add_password_with_new_definition
SET add_password_definition = @new_definition -- 将新定义的语句，收集起来
WHERE proc_name = @proc_name
END
IF @new_definition IS NULL -- 拼串时发生错误时 NULL ,new_definition_error 查找原因
BEGIN
INSERT INTO tempdb.dbo.new_definition_error
( proc_name, remark )
SELECT @proc_name ,
'1'
END
COMMIT TRAN new_definition
END TRY
BEGIN CATCH
ROLLBACK TRAN new_definition
INSERT INTO tempdb.dbo.new_definition_error --发生错误时，回滚；收集错误信息
( proc_name, errmsg )
SELECT @proc_name ,
ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM c INTO @proc_name, @definition
END
CLOSE c
DEALLOCATE c
--存在未加密成功的过程，强制人为返回错误
IF EXISTS ( SELECT *
FROM sys.sql_modules s ,
sys.objects o
WHERE o.object_id = s.object_id
AND o.type = 'P'
AND o.name LIKE 'pr_%'
and s.definition is not null )
BEGIN
select '本次加密操作未完全成功！请手动执行加密
SELECT OBJECT_NAME(a.object_id) ,
a.definition
FROM wfp.sys.sql_modules a ,
wfp.sys.objects b
WHERE a.definition IS NOT NULL and b.name LIKE ''pr_%''
AND a.object_id = b.object_id
AND b.type = ''P''
查看失败过程,然后手工操作
' as error_msg
END