USE [DQHLGPS]
GO
/****** 对象: StoredProcedure [dbo].[PROC_GET_INSERTCODE] 脚本日期: 08/27/2008 15:27:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--存储过程: PROC_GET_INSERTCODE
--功 能: 根据表名及条件生成INSERT语句
--完成日期: 2008-8-25
--=====================================================
ALTER PROC [dbo].[PROC_GET_INSERTCODE]
@TABLENAME VARCHAR(256),
@TERM NVARCHAR(4000)=''
AS
BEGIN
declare @SQL varchar(8000)
declare @SQLValues varchar(8000)
set @SQL =' ('
set @SQLValues = 'values (''+'
select @SQLValues = @SQLValues + cols + ' + '','' + ' ,@SQL = @SQL + '[' + name + '],'
from
(select case
when xtype in (48,52,56,59,60,62,104,106,108,122,127)
then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
when xtype in (58,61)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end' //就是这行,不知道怎么改呀!
when xtype in (167)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (231)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (175)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
when xtype in (239)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
else '''NULL'''
end as Cols,name
from syscolumns
where id = object_id(@tablename)
) T
set @SQL ='select ''INSERT INTO ['+ @tablename + ']' + left(@SQL,len(@SQL)-1)+') ' + left(@SQLValues,len(@SQLValues)-4) + ')'' from '+@tablename +' '+@term
exec (@SQL)
end
convert(varchar(19),name,120)
关键字 'convert' 附近有语法错误。怎么改呀?
-------------------------------
08/28/08 8:18:34 AM