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,1) NOT NULL,
			[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, 2) NULL,
			[TalkTimeLen] [decimal](18, 2) NULL,
			[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, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 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