修改存储过程的指定参数类型

最新推荐文章于 2024-09-24 22:33:45 发布

原创最新推荐文章于 2024-09-24 22:33:45 发布 · 839 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了如何使用SQL脚本来检查并可能修改特定存储过程的源码，包括换行、注释处理及参数类型判断。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


use LineCheckDB
go

--查看存储过程源码（proc/view/function/trigger都可）
alter procedure mp_helptext
    @name varchar(255)
as
declare @object_id int,
    @sourcecode varchar(max),
    @line varchar(max),
    @end int,
    @rn varchar(2),
    @tab varchar(1)

--declare @source table(
--  source varchar(max)
--)

set @rn = char(13)+char(10)
set @tab = char(9)

begin

--从sys.sql_modules获得无换行源码
select @sourcecode = definition from sys.sql_modules where object_id=object_id(@name) 

--换行
while(charindex(@rn,@sourcecode)!=0)
begin
    set @end=charindex(@rn,@sourcecode)
    set @line = replace(substring(@sourcecode,1,@end-1),@tab,@tab+@tab)
    if(charindex('create',@line)<>0 and (charindex('proc',@line)<>0 or charindex('view',@line)<>0 or charindex('function',@line)<>0 or charindex('trigger',@line)<>0))
    begin
        set @line = replace(@line,'create','alter')
    end
    if( PATINDEX('[--]%', @line) <= 0 and (
        PATINDEX('%@checkitem_serial_number%', @line) > 0 or 
        PATINDEX('%@checkitem_scan%', @line) > 0   ))
        set @line = REPLACE(@line, '(30)', '(MAX)')
        --insert into @source(source) values(@line)
    print @line
    set @end = @end + 2
    set @sourcecode = substring(@sourcecode,@end,len(@sourcecode))
end

--insert into @source(source) values(@sourcecode)
--insert into @source(source) values('GO')
--print @line
print ('GO')
--select * from @source 
end
go

--判断该存储过程是否满足要求
create procedure mp_name
    @name varchar(255),
    @output int output--返回1表示，该存储过程满足要求
as
declare @object_id int,
    @sourcecode varchar(max),
    @line varchar(max),
    @end int,
    @rn varchar(2),
    @tab varchar(1)

set @rn = char(13)+char(10)
set @tab = char(9)

begin

select @sourcecode = definition from sys.sql_modules where object_id=object_id(@name)

while(charindex(@rn,@sourcecode)!=0)
begin
    set @end=charindex(@rn,@sourcecode)
    set @line = replace(substring(@sourcecode,1,@end-1),@tab,@tab+@tab)

    --需要修改的存储过程，输出1
    if( PATINDEX('[--]%', @line) <= 0 and (
        PATINDEX('%@checkitem_serial_number%', @line) > 0 or 
        PATINDEX('%@checkitem_scan%', @line) > 0   ))
    begin
        set @output = 1
        break
    end
    --  or (PATINDEX('[--]%', @line) > 0 and patindex('%[吖-座]%', @line) > 0)
    set @end = @end + 2
    set @sourcecode = substring(@sourcecode,@end,len(@sourcecode))
end

end
go

--修改存储过程的指定参数类型
declare @count int = 0
declare @output int = 0
declare @name varchar(250)
declare aa cursor for
select name from sys.objects where type = 'P' and name not like 'sp_%' and name <> 'mp_helptext' and name <> 'mp_name' order by name

begin
open aa
fetch next from aa into @name
while @@FETCH_STATUS = 0
begin 
    set @output = 0
    exec mp_name @name, @output output
    if(@output = 1)
    begin
        --print @name
        exec mp_helptext @name
    end
    fetch next from aa into @name
    set @count += @output
end

print @count
close aa
deallocate aa

end