存储过程中的varchar变量换成nvarchar.sql

很好的sql 文档,可能邦你成为sql 高手

--存储过程中的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