08-16-2019 02:17 AM
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.
08-16-2019 04:58 AM
08-16-2019 05:53 AM
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?
08-16-2019 06:27 AM
08-16-2019 06:53 AM
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.
01-17-2020 12:48 PM
Hi Simon.
Did you create a new table to catch the result of your store procedure? or what did you do?
I have this problem too, I execute a stored procedure and returns a new table (or string) with different variables. So, DB Get step ask for match a local variable with a DB variable.
Did you find a way to configure a DB Step?
Any recommendatios?
Warm regards.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide