--声明
USE ActiveInteractionData
go
drop PROCEDURE haoma
GO
CREATE PROCEDURE haoma
(
@CallFromNumber varchar(64),
@CallToNumber varchar(64),
@call varchar(64) OUTPUT
)
AS
IF exists(
SELECT top 1 CallFromNumber
FROM tblCallSummary
WHERE tblCallSummary.CallFromNumber=@CallFromNumber order by CallSummaryId desc )
begin
set @call=
(SELECT top 1 Agent2DeviceID
FROM tblCallSummary
where CallFromNumber=@CallFromNumber order by CallSummaryId desc)
end
else
begin
set @call =(select VDNNumber
from RoutingTable
where DID=@CallToNumber)
end
--执行
DECLARE @call varchar(64)
EXEC haoma '0966322796','33188004',@call OUTPUT
SELECT 'xingm'=@call
GO
--说明 该存储过程是为了实现电话的只能跟随设计的 '0966322796','33188004' 是传入的号码和VDN 如果在数据库的表tblCallSummary里面找到找到该电话,就把接该电话的坐席员赋给返回返回值,表明找到了接电话的人
如果没有找到该号码说明第一次打该电话 把vdn赋给该值33188004对应的有vdn 主要要加引号 表明传的是字符串
后来客户要求改 他们传的是数值 我就做了以下更改
--声明存储过程 USE ActiveInteractionData go drop PROCEDURE haoma GO CREATE PROCEDURE haoma ( @CallFromNumber bigint, @CallToNumber bigint, @call varchar(64) OUTPUT ) AS IF exists( SELECT top 1 CallFromNumber FROM tblCallSummary WHERE CallFromNumber = convert(varchar(64),@CallFromNumber) order by CallSummaryId desc ) begin set @call= (SELECT top 1 Agent2DeviceID FROM tblCallSummary where CallFromNumber = convert(varchar(64),@CallFromNumber) order by CallSummaryId desc) end else begin set @call =(select VDNNumber from RoutingTable where DID=@CallToNumber) end --执行改过程 DECLARE @call varchar(64) EXEC haoma 0966322796,33188004,@call OUTPUT SELECT 'xingm'=@call GO
这样存在一个问题 就是在数值转换的时候 你传的0966322796 在传给数值的形式参数的时候,会自动把0去掉造成找不到该号码