DECLARE @Sql VARCHAR(1000) DECLARE @xtype VARCHAR(10) = 'FN' SET @Sql = ' if exists(select 1 from sysobjects where id=object_id(''DFC_QtyAdd'') and xtype=''' + @xtype + ''' ) begin drop function dbo.DFC_QtyAdd end'PRINT @Sql
declare @sql varchar(1000),@xtype char(2) select @xtype='FN' select @sql='if exists(select 1 from sysobjects where id=object_id(''DFC_QtyAdd'') and xtype='''+@xtype+''') begin drop function dbo.DFC_QtyAdd end' exec(@sql)
--需求是这样的 select @sql='select ''if exists(select 1 from sysobjects where id=object_id(''+a.name+'')) and xtype='+@xtype+' begin drop function dbo.''+a.name+'' end'' as b' select @sql=@sql+' from sysobjects a where a.xtype='''+@xtype+''' order by a.name' exec(@sql)--我的查询结果为 if exists(select 1 from sysobjects where id=object_id(DFC_QtyAdd)) and xtype=FN begin drop function dbo.DFC_QtyAdd end--需要变成 if exists(select 1 from sysobjects where id=object_id('DFC_QtyAdd')) and xtype='FN' begin drop function dbo.DFC_QtyAdd end --就是引号加不上去
DECLARE @Sql VARCHAR(1000) DECLARE @xtype VARCHAR(10) = 'FN' DECLARE @name VARCHAR(100) = 'DFC_QtyAdd' SET @Sql = ' if exists(select 1 from sysobjects where id=object_id(''' + @name + ''') and xtype=''' + @xtype + ''' ) begin drop function dbo.' + @name + ' end'PRINT @Sql
--这句帮我改一下 select @sql='select ''if exists(select 1 from sysobjects where id=object_id(''+a.name+'')) and xtype='+@xtype+' begin drop function dbo.''+a.name+'' end'' as b' select @sql=@sql+' from sysobjects a where a.xtype='''+@xtype+''' order by a.name'
magician547,我想自动生成删除函数的语句,有没有其他的方法?
数据库里有很多函数,比如函数DFC_QtyAdd、DFC_QtyDtoS、DFC_UnitConvert等,我想在Microsoft SQL Server Management Studio里执行 Exec(@sql) 后出来查询结果如下:if exists(select 1 from sysobjects where id=object_id('DFC_QtyAdd')) and xtype='FN' begin drop function dbo.DFC_QtyAdd end if exists(select 1 from sysobjects where id=object_id('DFC_QtyDtoS')) and xtype='FN' begin drop function dbo.DFC_QtyDtoS end if exists(select 1 from sysobjects where id=object_id('DFC_UnitConvert')) and xtype='FN' begin drop function dbo.DFC_UnitConvert end ……
DECLARE @Sql VARCHAR(1000)
DECLARE @xtype VARCHAR(10) = 'FN'
SET @Sql = '
if exists(select 1 from sysobjects where id=object_id(''DFC_QtyAdd'') and xtype=''' + @xtype + ''' )
begin
drop function dbo.DFC_QtyAdd
end'PRINT @Sql
select @xtype='FN'
select @sql='if exists(select 1 from sysobjects where id=object_id(''DFC_QtyAdd'') and xtype='''+@xtype+''') begin drop function dbo.DFC_QtyAdd end'
exec(@sql)
select @sql='select ''if exists(select 1 from sysobjects where id=object_id(''+a.name+'')) and xtype='+@xtype+' begin drop function dbo.''+a.name+'' end'' as b'
select @sql=@sql+' from sysobjects a where a.xtype='''+@xtype+''' order by a.name'
exec(@sql)--我的查询结果为
if exists(select 1 from sysobjects where id=object_id(DFC_QtyAdd)) and xtype=FN begin drop function dbo.DFC_QtyAdd end--需要变成
if exists(select 1 from sysobjects where id=object_id('DFC_QtyAdd')) and xtype='FN' begin drop function dbo.DFC_QtyAdd end
--就是引号加不上去
DECLARE @Sql VARCHAR(1000)
DECLARE @xtype VARCHAR(10) = 'FN'
DECLARE @name VARCHAR(100) = 'DFC_QtyAdd'
SET @Sql = '
if exists(select 1 from sysobjects where id=object_id(''' + @name + ''') and xtype=''' + @xtype + ''' )
begin
drop function dbo.' + @name + '
end'PRINT @Sql
--这句帮我改一下
select @sql='select ''if exists(select 1 from sysobjects where id=object_id(''+a.name+'')) and xtype='+@xtype+' begin drop function dbo.''+a.name+'' end'' as b'
select @sql=@sql+' from sysobjects a where a.xtype='''+@xtype+''' order by a.name'
magician547,我想自动生成删除函数的语句,有没有其他的方法?
数据库里有很多函数,比如函数DFC_QtyAdd、DFC_QtyDtoS、DFC_UnitConvert等,我想在Microsoft SQL Server Management Studio里执行 Exec(@sql) 后出来查询结果如下:if exists(select 1 from sysobjects where id=object_id('DFC_QtyAdd')) and xtype='FN' begin drop function dbo.DFC_QtyAdd end
if exists(select 1 from sysobjects where id=object_id('DFC_QtyDtoS')) and xtype='FN' begin drop function dbo.DFC_QtyDtoS end
if exists(select 1 from sysobjects where id=object_id('DFC_UnitConvert')) and xtype='FN' begin drop function dbo.DFC_UnitConvert end
……