Trying to get a return value from a Stored Procedure

Unanswered Question
May 2nd, 2010

Hi folks,


I have been tasked with something I thought was simple, but apparently its not that simple.  I am trying to call a Stored Procedure in UCCX that will do some calculations and returns back an integer value.  The stored procedure call I have is:


DECLARE @return_status int
EXEC @return_status = dbo.CTTLoginPhone @CTTId = $EmployeeID , @PIN = $EmployeePIN , @PhoneNumber = $CallingPhoneNumber ;SELECT 'Return_Status' = @return_status


When I run this in this the editor, I get back one row.  If I run this in the debugger, I do not get any errors back and the stored procedure runs fine, however the Return_Status is not set either.  What am I doing wrong?


I have defined Return_Status with intial value of 0 and the stored proc should be returning back 1 or 2, but the Return_Status remains as 0

 

Can someone please let me know if I need a DB GET after a DB READ or can I just use the DB READ standalone when calling stored procedure.


All help will be really appreciated.


Thank you.

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
mansoor777 Sun, 05/02/2010 - 09:34

Hi Geoff,


Thanks for the response. What do I put in my DB GET?


I tried creating a dummy table in the DB with just one column and one datarow and in the DB GET I am just mapping the dummy column to a dummy variable.  Still no go with the Return_Status.  Now when the DB get runs, it gives me an error.  The error is: %MIVR-DB_STEPS-3-EXECUTE_SQL_EXCEPTION:Error during executing SQL Update: Task ID=18000000691,Step Name=DB Get,SQL State=S0022,SQL Error Code=0

mansoor777 Sun, 05/02/2010 - 09:47

Hi Geoff,


I searched through the entire UCCX.NET site for the keyword "STORED" and

went through every single post before posting my message. I have even

created a dummy table in the DB with one dummy record and tried hooking that

up in DB GET but I get SQL error. When I run my script the STORED PROC is

called just fine and the appropriate entries are made in the DB, its just

that the return value is not set.


Your help would be greatly appreciated.


PS: I even looked at the script repository and did not find any stored proc

calls.


Thanks.

I am having the same issue here... any help would be greatly appreciated. I am on UCCX 8


I am getting 1 row returned when I click "TEST" in the READ step but the DB GET step is going into the SQL Error branch.


Here are some details and I have attached screen shots of my script DB steps.....



I have a Stored Procedure that is very simple...


CREATE PROCEDURE sp_test AS
Select 'N';
GO


Ive created a dummy table with 1 field..

TABLE: testTable

FIELD: code




Let me know if there are any more details I can provide....

jeeshthomas Fri, 08/03/2012 - 16:05

Hi Tsuntken,

Were you able to find a solution to this? Can you kindly provide me with your solution script?

Thank you for your time and efforts.

Regards

Jeesh

josh.lipton Mon, 08/13/2012 - 11:49

Hi,


I am having the same issue, The big problem i am having is i dont understand what this "dummytable" is?  Am I supposed to make the table with in SQL with just one colume?  How do i write the data back to that table? 


Thank you for your help


Josh

Actions

This Discussion