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
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)
@am_Start = AM_Start, @Lunch_Start = Lunch_Start, @pm_Start = PM_Start, @Eve_Start = Eve_Start
Inst='Someone' AND Date=@Today
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'
SET @Status ='None'
In my UCCX Script, I have a DB Read step which does this :-
DECLARE @Status varchar(10)
@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.
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?
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.