--存储过程中的varchar变量换成nvarchar
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_convert]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_convert]
GO
--创建一个函数,替换指定字符串中的varchar为nvarchar
create function f_convert(@text nvarchar(4000))
returns varchar(8000)
as
begin
declare @i1 int,@i2 int,@i3 varchar(20),@re varchar(8000)
select @i1=patindex('% varchar(%)%',@text)
,@i2=charindex(')',@text,@i1)
,@i3=substring(@text,@i1+9,@i2-@i1-9)
,@re=''
while @i1>0 and @i2>0
begin
if isnumeric(@i3)=1
if @i3>4000
select @re=@re+left(@text,@i1)+'nvarchar(4000)'
else
select @re=@re+left(@text,@i1)+'nvarchar('
+substring(@text,@i1+9,@i2-@i1-8)
else
select @re=@re+left(@text,@i2)
select @text=stuff(@text,1,@i2,'')
,@i1=patindex('% varchar(%)%',@text)
,@i2=charindex(')',@text,@i1)
,@i3=substring(@text,@i1+9,@i2-@i1-9)
end
return(@re+@text)
end
go
--调用函数实现转换处理:
declare @id int,@name sysname
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
declare tb cursor local for
select top 1 id,'['+replace(name,']',']]')+']' from sysobjects a
where xtype='P' and status>=0 and exists(
select 1 from syscomments
where id=a.id and charindex('varchar(',text)>0)
open tb
fetch tb into @id,@name
while @@fetch_status=0
begin
select @s1='',@s2='',@s3=''
select @s1=@s1+',@'+cast(colid as varchar)+' varchar(8000)'
,@s2=@s2+';select @'+cast(colid as varchar)
+'=f_convert(text) from syscomments where id='
+cast(@id as varchar)+' and colid='+cast(colid as varchar)
,@s3=@s3+'+@'+cast(colid as varchar)
from syscomments
where id=@id
order by colid
select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,1,'')
exec('declare '+@s1+'
'+@s2+'
drop proc '+@name+'
exec('+@s3+')
')
fetch tb into @id,@name
end
close tb
deallocate tb
257.存储过程找那个的varchar表里换成nvchar
最新推荐文章于 2023-12-06 20:45:00 发布