cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
109
Views
0
Helpful
4
Replies
Beginner

Running Stored Procedures and using the response in a UCCX variable

I am trying to run a stored procedure in a UCCX script, and then I want to use the response from the stored procedure in the UCCX script.

 

My Stored procedure looks something like  :-

 

ALTER PROCEDURE Get_Date_Time @Status varchar(10) OUTPUT
AS
BEGIN

DECLARE @Today as date
SET @Today = GETDATE()
DECLARE @Now as time
SET @Now = CONVERT(TIME, GETDATE())

DECLARE @am_Start as varchar(50)
DECLARE @Lunch_Start as varchar(50)
DECLARE @pm_Start as varchar(50)
DECLARE @Eve_Start as varchar(50)

SELECT
@am_Start = AM_Start, @Lunch_Start = Lunch_Start, @pm_Start = PM_Start, @Eve_Start = Eve_Start
FROM
Special_Days
WHERE
Inst='Someone' AND Date=@Today

IF @Now<@AM_Start
SET @Status = 'Evening'
ELSE IF @Now<@Lunch_start
SET @Status = 'AM'
ELSE IF @Now<@PM_Start
SET @Status ='Lunchtime'
ELSE IF @Now<@Eve_Start
SET @Status ='PM'
ELSE
SET @Status ='None'
END;

 

In my UCCX Script, I have a DB Read step which does this :-

 

DECLARE @Status varchar(10)
EXEC Get_Date_Time
@Status = @Status OUTPUT;

SELECT @Status as N'Status'

 

If I click Test I get healthy looking integer 1 in the 'number of rows returned' box.

 

But how do I get access to the data that is returned?  I have a string variable defined called Status but this just equates to an empty string after I run the script.

 

I don't get any errors when running, and it all validates ok.

 

So near, yet so far away!

Any help greatly appreciated.

 

4 REPLIES 4
Highlighted
Enthusiast

Re: Running Stored Procedures and using the response in a UCCX variable

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?
Beginner

Re: Running Stored Procedures and using the response in a UCCX variable

Hi Mike,

 

No I'm not.... and this is where I get confused, because the DB Get step requires me to link the variable to a database field, which I don't need.

 

The Stored Procedure is (I'm hoping) returning a string, which isn't actually a database field or anything. Its just an indication of a state, which is dependent on the database it has queried. 

 

It's like I have a DB with a value of 2 in a field, and I run a stored procedure, to compare the value 3 to the database field, and the stored procedure should then return "less than", "equal to", "more than".

 

Am I trying to do something that isn't possible perhaps?

Enthusiast

Re: Running Stored Procedures and using the response in a UCCX variable

I am wondering if you can create a temp table within the SP. I have no idea if that will work.

I have what looks like a similar UCCX script that executes a stored procedure for a queue mode and time of day, but I utilize a static table and overwrite the table with a simple integer.

Table is just like this:
Queue ID QueueMode
1 1
2 7
3 8

And in the UCCX script, 1-8 = the specific modes.

Bottom line, if you can get some form of a table created to reference, that might be your only option.
Beginner

Re: Running Stored Procedures and using the response in a UCCX variable

Yes, I read somewhere else that this could be the technique. But it rather makes a mockery of the suggestion you can run a stored procedure and pass back a variable result, because you clearly can't!

Thanks for the suggestions anyway.

CreatePlease to create content
Content for Community-Ad
August's Community Spotlight Awards