更新日志存儲過程

--更新日志存儲過程
--作者:yoncen 2005/12/31
CREATE PROCEDURE up_UpdateLog
(
@databasename sysname, --數據庫名
@tablename sysname, --表名
@fieldname sysname, --字段名
@updatetext nvarchar(1000) --更新內容
)
AS
SET NOCOUNT ON
declare @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數據類型日志更新存儲過程.