December 8, 2009 at 11:18 am
Alright gurus.
Here's another stumper for me.
alter PROC casemanagement_Call_Insert
AS
BEGIN
DECLARE@state VARCHAR(3)
DECLARE@leadTypeVARCHAR(3)
DECLARE @dnis VARCHAR(5)
DECLARE @fileNumber VARCHAR(9)
DECLARE @tzstring VARCHAR(10)
DECLARE @hphone VARCHAR(10)
DECLARE @wphone VARCHAR(10)
DECLARE @cphone VARCHAR(10)
DECLARE @loginname VARCHAR(15)
DECLARE @firstVARCHAR(20)
DECLARE @last VARCHAR(20)
DECLARE @bizGroup VARCHAR(20)
DECLARE@currstatus VARCHAR(25)
DECLARE @marketer VARCHAR(35)
DECLARE@email VARCHAR(50)
DECLARE @sort VARCHAR(50)
DECLARE @note VARCHAR(200)
DECLARE @areacode CHAR(3)
DECLARE @lclphonenum CHAR(7)
DECLARE @gender BIT
DECLARE@csidSMALLINT
DECLARE@productTypeSMALLINT
DECLARE@qOneCountSMALLINT
DECLARE@recSrcCodeSMALLINT
DECLARE @tz INT
DECLARE @que INT
DECLARE@priorityINT
DECLARE @xtelelink INT
DECLARE @client_idINT
DECLARE @address_id INT
DECLARE @phone_id INT
DECLARE@currstatusDateDATETIME
DECLARE@dtLoadedDATETIME
DECLARE @callBackDateDATETIME
/******************************************************************************
**** Recover leads from the casemanagement_stage table **************************
******************************************************************************/
DECLARE casemanagement_cursor CURSOR READ_ONLY FOR
SELECTp.Filenum,p.DateLoaded,p.LastName,p.FirstName,
p.Sex,p.State,p.HomePhone,p.WorkPhone,
p.CellPhone,p.Email,p.currStatus,p.currStatusDate,
p.DNIS,p.primary_Client_id,p.Address_id,p.Phone_id,
p.LoginName,p.RecordSourceCode
FROMkinks.dev.dbo.casemanagement_stage p
LEFT OUTER JOIN
kinks.dev.dbo.casemanagement_tracker t
ONt.fileNumber = p.fileNum
ANDt.dateLoaded = p.dateLoaded
WHEREt.fileNumber IS NULL
OPEN casemanagement_cursor
FETCH NEXT FROM casemanagement_cursor INTO
@fileNumber, @dtLoaded,@last, @first,
@gender, @state,@hphone,@wphone,
@cphone,@email,@currstatus,@currstatusDate,
@dnis,
@client_id,@address_id,@phone_id,
@loginName,@recSrcCode
WHILE @@FETCH_STATUS = 0
BEGIN
/******************************************************************************
**** Phone Number manipulations ***********************************************
******************************************************************************/
-- eliminate duplicate copies of the phone number
IF (( @hphone = @wphone) AND (@hphone IS NOT NULL))
SELECT @wphone = NULL
IF (( @hphone = @cphone) AND (@hphone IS NOT NULL))
SELECT @cphone = NULL
IF (( @wphone = @cphone) AND (@wphone IS NOT NULL))
SELECT @cphone = NULL
-----------------------------------------------------------------------
-- fill in phone number gaps
IF (( @hphone IS NULL) AND (@cphone IS NOT NULL))
BEGIN
SELECT @hphone = @cphone
SELECT @cphone = NULL
END
IF (( @hphone IS NULL) AND (@wphone IS NOT NULL))
BEGIN
SELECT @hphone = @wphone
SELECT @wphone = NULL
END
IF (( @wphone IS NULL) AND (@cphone IS NOT NULL))
BEGIN
SELECT @wphone = @cphone
SELECT @cphone = NULL
END
-----------------------------------------------------------------------
-- select a phone number to be the primary number
IF (@hphone IS NOT NULL)
BEGIN
SELECT @areacode = SUBSTRING(@hphone,1,3)
SELECT @lclphonenum = SUBSTRING(@hphone,4,7)
END
ELSE IF (@wphone IS NOT NULL)
BEGIN
SELECT @areacode = SUBSTRING(@wphone,1,3)
SELECT @lclphonenum = SUBSTRING(@wphone,4,7)
END
ELSE IF (@cphone IS NOT NULL)
BEGIN
SELECT @areacode = SUBSTRING(@cphone,1,3)
SELECT @lclphonenum = SUBSTRING(@cphone,4,7)
END
-----------------------------------------------------------------------
-- set input phone numbers to strings so phone number search will work
SELECT @hphone = ISNULL(@hphone,'abcdefghij')
SELECT @wphone = ISNULL(@wphone,'abcdefghij')
SELECT @cphone = ISNULL(@cphone,'abcdefghij')
-- don't add if number already active
IF NOT EXISTS (
SELECTNULL
FROM kinks.dev.dbo.casemanagement_list l
JOIN kinks.dev.dbo.telescrp t
ON t.xtelelink = l.xtelelink
WHERE ((l.phone_primary_home IN (@hphone,@wphone,@cphone))
OR(l.phone_primary_work IN (@hphone,@wphone,@cphone))
OR(l.phone_primary_cell IN (@hphone,@wphone,@cphone))))
BEGIN
-- replace bogus numbers with nulls
IF ( @hphone = 'abcdefghij') SELECT @hphone = NULL
IF ( @wphone = 'abcdefghij') SELECT @wphone = NULL
IF ( @cphone = 'abcdefghij') SELECT @cphone = NULL
/******************************************************************************
**** define the time zone parameters ******************************************
******************************************************************************/
-- if areacode is a toll free number, get the timezoneoffset from the client's state
SELECT @tz = NULL
SELECT @tzstring = NULL
IF EXISTS (
SELECT areacode
FROM DIALERDB.Dialer_Master.dbo.CM_TollFreeAreaCodes
WHERE areacode = @areacode )
BEGIN
SELECT @tz = TimeZoneOffset
FROM DIALERDB.Dialer_Master.dbo.CM_TimeZonesByState
WHERE State = @state
END
ELSE
BEGIN
SELECT @tz = TimeZoneOffset
FROM DIALERDB.Dialer_Master.dbo.CM_TimeZones
WHERE areacode = @areacode
END
-- default to most allowable time for prospect package to arrive
-- @tz = 5 is the eastern time zone
IF ( @tz IS NULL )
SELECT @tz = 5
ELSE IF (@tz > 11 )
SELECT @tz = 11
ELSE IF (@tz < 4 )
SELECT @tz = 4
SELECT @tzstring = TimeZone_Str
FROM DIALERDB.Dialer_Master.dbo.CM_TimeZoneStr
WHERE timezone = @tz
/******************************************************************************
**** define the queue *********************************************************
******************************************************************************/
BEGIN
IF (@currstatus IN ('App Hold', 'App Hold2', 'App Reopened', 'App complete'))
INSERT INTO kinks.Dev.dbo.Queue2 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
ELSE IF (@currstatus in ('Exam Complete', 'Exam Complete No App'))
INSERT INTO kinks.dev.dbo.Queue3 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
ELSE IF (@currstatus in ('Underwriting'))
INSERT INTO kinks.dev.dbo.Queue4 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
ELSE IF (@currstatus in ('Underwriting-APS'))
INSERT INTO kinks.dev.dbo.Queue4 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
ELSE IF (@currstatus in ('Underwriting-Tentative offer', 'Other Than Applied'))
INSERT INTO kinks.dev.dbo.Queue5 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
ELSE IF (@currstatus in ('Approved as Applied', 'Better Than Applied For', 'Pending Issue', 'Pending Reissue'))
INSERT INTO kinks.dev.dbo.queue6 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
ELSE IF (@currstatus in ('Underwriting-Tentative offer', 'Other Than Applied'))
Insert into kinks.dev.dbo.queue7 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
else if (@currstatus in ('Delivery Requirements'))
insert into kinks.dev.dbo.queue8 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
else if (@currstatus in ('In Force'))
insert into kinks.dev.dbo.queue9 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
ElseInsert into kinks.dev.dbo.queue1 (XTeleLink, Sort, PhoneNum, xCheckOut)
VALUES (@xtelelink, @sort, @hphone, 0)
END
/******************************************************************************
**** get the latest note ******************************************************
******************************************************************************/
SELECT @sort = CONVERT(VARCHAR,@dtLoaded,120)
SELECT @note = NULL
SELECT @note = note
FROM beatles.Client_Manager_Production.dbo.t_note
WHERE fileNum = @fileNumber
AND sortorder = (SELECT min(sortorder)
FROM beatles.Client_Manager_Production.dbo.t_note
WHERE fileNum = @fileNumber)
/******************************************************************************
**** insert the call **********************************************************
******************************************************************************/
IF (@callBackDate IS NULL)SELECT @callBackDate = getDate()
EXEC casemanagement_Call_Insert
@dnis,@fileNumber,@first,@last,
@gender,@state,@hphone,@wphone,
@cphone,@currstatusDate,@note,
@client_id, @address_id,
@phone_id,@tz,@tzstring,@xtelelink OUT
INSERT INTO kinks.dev.dbo.TeleScrp (
XTeleLink,
XPriority,
XTimeZone,
XCalls,
XQnumber,
XOperator,
Xcallback)
VALUES (
@xtelelink,
2,
(@tz)*(-60),
0,
@que,
@loginname,
@callBackDate)
END
CLOSE casemanagement_cursor
DEALLOCATE casemanagement_cursor
END
END
(1 row(s) affected)
Msg 8146, Level 16, State 1, Procedure casemanagement_Call_Insert, Line 0
Procedure casemanagement_Call_Insert has no parameters and arguments were supplied.
So. Can anyone figure this out? Cuz I'm locked.
Thanks y'all!
December 8, 2009 at 12:31 pm
Do this,
Create a new database in your Test Server (Only on your Test Server)
And run the exact script that you posted in this thread and run it. See what the error you get.
I get that error when I run in my system, am I missing anything???:w00t:
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 8, 2009 at 12:48 pm
Yeh,
same error.
December 8, 2009 at 12:50 pm
Guess why?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 8, 2009 at 12:53 pm
SQLRookie 6502 (12/8/2009)
So. Can anyone figure this out? Cuz I'm locked.
Sure. A very quick find shows that you're calling the proc from within the proc, but calling it with 17 parameters. It's right at the bottom of the stored proc
/******************************************************************************
**** insert the call **********************************************************
******************************************************************************/
IF (@callBackDate IS NULL) SELECT @callBackDate = getDate()
EXEC casemanagement_Call_Insert
@dnis, @fileNumber, @first, @last,
@gender, @state, @hphone, @wphone,
@cphone, @currstatusDate, @note,
@client_id, @address_id,
@phone_id, @tz, @tzstring, @xtelelink OUT
INSERT INTO kinks.dev.dbo.TeleScrp (
XTeleLink,
XPriority,
XTimeZone,
XCalls,
XQnumber,
XOperator,
Xcallback)
VALUES (
@xtelelink,
2,
(@tz)*(-60),
0,
@que,
@loginname,
@callBackDate)
END
CLOSE casemanagement_cursor
DEALLOCATE casemanagement_cursor
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 2:37 pm
Yeh, just figured it out.
lil typo there, calling itself and not the other proc.
Fixxt. thanks, fellas!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply