SQL Stored Procedure?????

Version 1
    This document was generated from CDN thread

    Created by: Larry Harris on 29-09-2011 03:29:11 AM
    I was give this stored procedure (see below) and told to design a script which prompts callers to enter their number and, if the customer's phone number is located in the DB, read back a time which is one hour before and two hours after the actual time stored in the sql database to the customer. Any ideas as to how to accomplish this?  



    The db call is to CustANI 


    PhoneNumber <Varchar(25)>

    ScheduleDate <datetime>  Will default to todays date if no date is passed

    Return: Table 

    StatusCode <boolean>    1=Found -1=Unfound

    TimeWindowStart <datetime>

    TimeWindowEnd <datetime>

    IsAfterRouteEnd <boolean> 1=Yes 2=No  for ex: ETA is 7PM but we stop at 4:30PM

    GroupName <varchar(255)>  Diagnostic purposes: if its a "Special" route we don't return an ETA

    Pickup found...

    declare @Phone varchar(25)

    set @Phone = '3102202222 <tel:3102202222>'

    declare @ScheduleDate datetime  --If null we use today's date

    set @ScheduleDate = NULL

    EXECUTE . @Phone, @ScheduleDate 

     StatusCode     TimeWindowStart      TimeWindowEnd         IsAfterRouteEnd     GroupName    

     -------------  -------------------  --------------------  ------------------  ------------ 

     0              1/1/1900 8:30:00 AM  1/1/1900 11:30:00 AM  0                   Standard     

    Not Found ...

    declare @Phone varchar(25)

    set @Phone = '3102252622 <tel:3102252622>'  --bogus number

    declare @ScheduleDate datetime  --If null we use today's date

    set @ScheduleDate = NULL

    EXECUTE . @Phone, @ScheduleDate 

     StatusCode     TimeWindowStart     TimeWindowEnd     IsAfterRouteEnd     GroupName    

     -------------  ------------------  ----------------  ------------------  ------------ 

     -1             (null)              (null)            (null)              (null)
    <font face="Calibri">






    Subject: RE: SQL Stored Procedure?????
    Replied by: Steven Pawlak on 29-09-2011 09:19:08 AM

    That code can be replicated in IVR with the steps. Looks like you are passing 2 in and getting 5 back out on the call. The real trick is the call to EXECUTE dbo.custANI. The DB subsystem of the current Premium versions do not call Stored Procedures.

    If the CustANI stored procedure is simple enough, you could just recreate that using the DB steps. I would put all of this into a SubScript for code management. Then call the SubScript where needed, even if only in one place.

    If the CustANI stored procedure is not simple or they do not want it coded in 2 places, then the next best thing would be to write a simple ASP (pick a language) page that takes a HTTP POST/GET and use the Create URL document step to make a call to the ASP page, passing the 2 parameters. Then have the ASP page do the DB call and then have it pass back the data as an XML file. Then the IVR script can parse the data from the XML and proceed from there.

    Let me know if this helps.