--更新日志存儲過程--作者:yoncen 2005/12/31CREATE PROCEDURE up_UpdateLog(@databasename sysname, --數據庫名@tablename sysname, --表名@fieldname sysname, --字段名@updatetext nvarchar(1000) --更新內容)AS SET NOCOUNT ONdeclare @sqlcmd nvarchar(2000)select @sqlcmd = ''select @sqlcmd = @sqlcmd+'--檢查select into/bulkcopy狀態'select @sqlcmd = @sqlcmd+'CREATE TABLE #dboption(name VARCHAR(200))'+char(10)select @sqlcmd = @sqlcmd+'INSERT INTO #dboption'+char(10)select @sqlcmd = @sqlcmd+'EXECUTE sp_dboption '''+@databasename+''''+char(10)select @sqlcmd = @sqlcmd+'IF EXISTS(SELECT name FROM #dboption '+char(10)select @sqlcmd = @sqlcmd+' WHERE name=''select into/bulkcopy'''+char(10)select @sqlcmd = @sqlcmd+'EXECUTE sp_dboption '''+@databasename+''''select @sqlcmd = @sqlcmd+', ''select into/bulkcopy'', ''true'''+char(10)select @sqlcmd = @sqlcmd+'--更新日志'+char(10)select @sqlcmd = @sqlcmd+'DECLARE @ptrval binary(16)'+char(10)select @sqlcmd = @sqlcmd+'SELECT @ptrval = TEXTPTR('+@fieldname+')'+char(10)select @sqlcmd = @sqlcmd+'FROM '+@tablename+char(10)select @sqlcmd = @sqlcmd+'UPDATETEXT '+@tablename+'.'+@fieldname+' 'select @sqlcmd = @sqlcmd+'@ptrval 0 0 '''+@updatetext+''''+char(10)select @sqlcmd = @sqlcmd+'--清除臨時數據'+char(10)select @sqlcmd = @sqlcmd+'TRUNCATE TABLE #dboption'+char(10)select @sqlcmd = @sqlcmd+'DROP TABLE #dboption'EXECUTE(@sqlcmd)SET NOCOUNT OFF
--說明:這是一個通用的text數據類型日志更新存儲過程.
