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] [datetiR_772_11845@e] 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] [datetiR_772_11845@e] 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] [datetiR_772_11845@e] 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