How to execute a Stored Procedure

[anthoney_murphy]

I am attempting to use a DB Read step to execute a stored procedure on UCCX 8.  When I attempt to save the Read step I get an error for every variable I have in the SQL statement.  When removing the variables and hardcoding the information there is no issue.  Customer is getting antsy.  Any help would be appreciated.

Here is the SQL statement.

DECLARE @return_status int
EXECUTE @return_status = BEGIN.BEPA $strAcctNum, $strCustNum, $decBalance, $strFinalExtension, 'Entered through IVR', 0;
SELECT 'Return_Status' = @return_status

PS

I am not very well versed in SQL.

[Paulo Pereira]

Hello, Anthoney.

I'm sorry to see you were not able to find the information or assistance you were looking for here in the Contact Center community forum.

You may be able to find more help through the Cisco Developer Network.

Also, you might want to consider engaging Cisco Advanced Services via your account team to assist with UCCX custom scripting.

Thank you, and good luck.

-Paulo

[geoff]

Paulo,

What is the purpose of your response? You have provided the same response to a number of posts. Are you looking for posts that are 30 days or so old, and bumping them? Are you drumming up business for AS?

Regards,

Geoff

[Paulo Pereira]

Hi, Geoff.

Last week I reviewed all the threads posted to this forum in Feb and March ...   For a couple of the unanswered questions, I responded with the information that I had on hand.  For some that involved custom scripting/development, I figured a reference to Dev Support and/or Advanced Services would be better than no response at all (and as an added bonus, my response would "bump" the thread and perhaps give it a "second chance" to be answered by the community).  But for the vast majority of the unanswered questions that I found, I am in the process of trying to find someone within the TAC organization to answer them.

Best regards,

Paulo

[geoff]

That's a great service. We do see some threads just roll off without an answer, so bumping them after a month or so is going to help. I'm delighted to see Cisco taking this active role.

Regards,

Geoff

[Clifford McGlamry]

Anthony,

What kind of database are you connecting to?  I'm guessing it's Oracle based on the syntax used.

Complex SQL statements don't work inside UCCX.  I'm not sure why, but in my experience anything requiring multiple steps of execution doesn't work.  What you posted has at least two.

In Microsoft SQL Server, a stored procedure might look like this:

create procedure spStoredProcedure

        @input   nvarchar(25)

AS

insert into myTable (myValue) values @input

You could then run the stored procedure by simply putting in   EXEC spStoredProcedure or just spStoredProcedure in the query window in UCCX.  This one doesn't return anything.  If I wanted it to return something I'd have to embed that in the SP.  But note, that you CANNOT use your parameter variables to get information back.  Oracle does this I think, but it's not supported in UCCX.

Cliff

[jeeshthomas]

Hi Cliff,

You mentioned in your post that if you wanted to return an output from the Stored procedure, then you could embed that in the SP. Will this work in UCCX 8.5 Premium? Can you kindly share an example script please?

Thank you

Regards

Jeesh

[Clifford McGlamry]

It will work in all versions I've seen to date from 3 through 8.5.

Sharing the script wouldn't show anything, because the script is kind of stupid about what's happening at the SQL level for the most part. 

But if you want to return something from our insert SP above, you could do this:

create procedure spStoredProcedure

        @input   nvarchar(25)

AS

insert into myTable (myValue) values @input

select 0 as myResult

In your script, you would execute the stored procedure in the script something like this:

exec spStoredProcedure 3

You'd have to create a dummy table in the database with a single field called myResult to map the return against. 

You'd retrieve myResult into whatever varialbe you wanted to map it to.  Obviously, the above example doesn't really buy you anything, but if you have logic in place to detect insert failures, etc. within the SQL statement, you can pass quite a bit of information back and forth if you want/need to.

Cliff

[Simon Edwards]

<< You'd have to create a dummy table in the database with a single field called myResult to map the return against. >>

Is this the standard way of returning a value to the UCCX script?  I'm really struggling to get a value sent back, despite having an SP that seems to work fine, and a DB Read step that runs the SP fine.

From what I can tell, you are saying you need to run an SP using a DB Read step, and the SP has to write the reply to a dummy database table, then you run a DB Get step in UCCX to get the response.

Is that correct?

[piyush aghera]

Under your DB Read - Successful step, are you setting a DB Get and entering the field selection values to link your returned results to the variable in the script?

Ref: https://community.cisco.com/t5/contact-center/running-stored-procedures-and-using-the-response-in-a-uccx/m-p/3909054