sqlserver 存储过程 参考

if object_id('CTI_SaveTaskCallRes','P') is not null  
    drop proc CTI_SaveTaskCallRes
go
CREATE proc CTI_SaveTaskCallRes
(
	@TaskID	INTEGER,--任务ID
	@ItemID	INTEGER,--条目ID
	@CallID INTEGER,--呼叫ID
	@CallRes INTEGER,--呼叫结果
	@RingTimeLen INTEGER,--振铃时长
	@QueuingTimeLen	INTEGER,--排队时长
	@TalkTimeLen INTEGER,--通话时长
	@DTMF varchar(50),--用户按键码
	@RecordName varchar(50),--录音文件名
	@CallTime varchar(50),--呼叫时间
	@CallMode INTEGER,--呼叫类型
	@TelNum varchar(20),--呼叫号码
	@Result varchar(20) output --返回更新后的结果
)
as
	--更新 录音记录表 start
	DECLARE @yy INT=DATEPART(yyyy,getdate());
	DECLARE @mm INT=DATEPART(mm,getdate());
	declare @Sql NVARCHAR(2000);
	declare @mouth varchar(2);
	if(@mm < 10)
		set @mouth = CAST(0 as varchar) + CAST(@mm as varchar);
	else
		set @mouth = CAST(@mm as varchar);
	declare @RecTableName varchar(50) = 'T_CTI_AgentRecord'+ cast(@yy as varchar) + @mouth;
   	if object_id(@RecTableName) is null  
        begin
	set @Sql = 'CREATE TABLE '+@RecTableName + '([ID] [bigint] IDENTITY(1,1) NOT NULL,[CallID] [bigint] NULL,[RecordFileName] 	[varchar](200) NULL,CONSTRAINT [PK_T_CTI_'+@RecTableName+'] PRIMARY KEY CLUSTERED([ID] ASC)WITH (PAD_INDEX  = OFF,STATIS	TICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]';
	exec(@Sql);
	end
	set @Sql ='insert into '+@RecTableName+' (CallID,RecordFileName) values('+CAST(@CallID AS VARCHAR)+','''+@RecordName+''')	';
	exec(@Sql);
	declare @RecordID integer;
    	if @@ERROR =0
   	 begin
		set @Sql = 'select @sRecordID = max(ID) from '+@RecTableName;
		Exec sp_executesql @Sql,N'@sRecordID int output',@RecordID OUTPUT;
	end
	-------end----------------------
	-------start update result table
	declare @TableName varchar(50);
	select @TableName=ItemTableName from T_CTI_Task where TaskID = @TaskID;
   	 if(@TableName<>'')
   	 begin
    	SET @Sql = N'update '+@TableName+' set CallID='+CAST(@CallID AS VARCHAR)+',CallTime='''+@CallTime+''',CallRes='+CAST(@Cal	lRes AS VARCHAR)+
    		',RingTimeLen='+CAST(@RingTimeLen AS VARCHAR)+',PlayTimeLen='+CAST(@QueuingTimeLen AS VARCHAR)+',TalkTimeLen='+
    		CAST(@TalkTimeLen AS VARCHAR)+',DTMF='''+@DTMF+ ''',RecordID='+CAST(@RecordID as VARCHAR)+',RecTableName='''+@Rec		TableName+''' where ID='+CAST(@ItemID AS VARCHAR(10));
	exec(@Sql);
   	 end
	-------end----------------------
	-------start update history table
	declare @HistoryTableName varchar(50) = 'T_CTI_CallHistory'+ cast(@yy as varchar) + @mouth;
	if object_id(@HistoryTableName) is null  
   	begin
		set @Sql = 'CREATE TABLE '+@HistoryTableName+'(
			[ID] [bigint] IDENTITY(1,[CallId] [bigint] NOT NULL,[CallMode] [int] NOT NULL,[TelNum] [varchar](20) NOT NULL,[State] [int] NULL,[CallTime] [datetime] NOT NULL,[RingTimeLen] [decimal](18,2) NULL,[PlayTimeLen] [decimal](18,[TalkTimeLen] [decimal](18,[DTMF] [varchar](50) NULL,[RecTableName] [varchar](50) NULL,[RecordID] [bigint] NULL,[CallRes] [int] NULL,[AddTime] [datetime] NULL,[y_TaskId] [bigint] NULL,[y_TmId] [int] NULL,[y_Content] [varchar](2000) NULL,[y_TelNmbTableName] [varchar](20) NULL,[y_TelNmId] [bigint] NULL,[y_Sfyy] [int] NULL,[y_Yysj] [datetime] NULL,[y_CallSource] [int] NULL,CONSTRAINT [PK_T_CTI_'+@HistoryTableName+'] PRIMARY KEY CLUSTERED 
			(		
				[ID] ASC
			)WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,ALLO			W_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]';
	exec(@Sql);
	end
	set @Sql ='insert into '+@HistoryTableName+' (CallID,TelNum,CallMode,CallTime,CallRes,RingTimeLen,PlayTimeLen,TalkTimeLen,DTMF,RecordID,RecTableName) values('+CAST(@CallID AS VARCHAR)+','+@TelNum+','+CAST(@CallMode AS VARCHAR)+','''+@CallTime	+''','+CAST(@CallRes 	AS VARCHAR)+','+CAST(@RingTimeLen AS VARCHAR)+','+CAST(@QueuingTimeLen AS VARCHAR)+','+CAST(@Talk	TimeLen AS VARCHAR)+','''+@DTMF+''','+CAST(@RecordID AS VARCHAR) + ',''' +@RecTableName+''')';
	exec(@Sql);
	-------end----------------------

GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!