| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Problem in Calling Stored Procedure |
« View previous topic :: View next topic » |
| Author |
Message
|
| sasad |
 Posted: Fri Jun 13, 2014 2:53 am Post subject: Problem in Calling Stored Procedure |
 |
|
Acolyte
Joined: 30 Apr 2014
Posts: 67
|
Dear all,
I am a novice user, and currently facing an issue in stored procedure call in esql.
I have created a stored procedure in esql as follow:
CREATE PROCEDURE BPM_GET_EMP_INFO(IN employeeId CHAR) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "BPM_GET_EMP_INFO";
and calling above mention function:
CALL BPM_GET_EMP_INFO(InputRoot.XMLNSC.ns:GetEmployeeInfo.ns:employeeId, Environment.employeeInfo[]);
The problem is that when I deploy application, stored procedure is called once then it never return the response.
Thanksss in advance.... |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Fri Jun 13, 2014 4:29 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Can you share your procedure skeleton (on the DB) and your odbc settings for the connection? Also broker version and db version? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| sasad |
| Posted: Tue Jun 17, 2014 4:21 am Post subject: |
|
|
Acolyte
Joined: 30 Apr 2014
Posts: 67
|
Thanks fjb_saper.
sorry for the late response.
I am using IB9 and SqlServer 2008 as database.
Following is the procedure, I am calling from ESQL.
-----------------------------
CREATE PROCEDURE BPM_GET_EMP_INFO (@EMP_ID VARCHAR(30)) AS
DECLARE
@ANNUAL_LEAVES INT , @HAJLEAVE INT ,@GRADE_YEAR_BLNC INT
BEGIN
EXEC BPM_GET_EMP_ANNLEAVE_BLNC @EMP_ID =@EMP_ID,@TOTAL=@ANNUAL_LEAVES OUTPUT;
EXEC BPM_GET_HAJLEAVE @EMP_ID =@EMP_ID,@VALID=@HAJLEAVE OUTPUT;
SELECT @GRADE_YEAR_BLNC=D.LVEDAYS
FROM ITQAN_ANNAULLEAVE_DAYS D
WHERE D.JOBGRADE=(
SELECT X.R_JOBGRADE
FROM HRR30240 X
WHERE X.EMPID_I =@EMP_ID);
SELECT M2.EMPLOYID AS 'EMPLOYEE_ID'
,M.EMPLOYEE_ARAB_NAME AS 'ARABIC_NAME'
,RTRIM(M2.FRSTNAME) + ' ' + M2.LASTNAME AS 'ENGLISH_NAME'
,BINFO.GENDER AS 'GENDER'
,BINFO.STRTDATE AS 'JOINING_DATE'
,M.R_JOBGRADE AS 'GRADE'
,M.EMAIL AS 'EMPLOYEE_EMAIL'
,BINFO.SUPERVISORCODE_I AS 'SUPERVISOR_CODE'
,SVISOR.SUPERVISOR AS 'SUPERVISOR_NAME'
,SVISOR_D.EMAIL AS 'SUPERVISOR_EMAIL'
,DEPTS.DEPRTMNT AS 'DEPT CODE'
,DEPTS.DSCRIPTN AS 'DEPARTMENT TITLE'
,@ANNUAL_LEAVES AS'ANNUAL_LEAVES_BALANCE'
,@HAJLEAVE AS'VALID HAJJ'
,@GRADE_YEAR_BLNC AS 'GRADE_YEAR_BLNC'
FROM UPR00100 M2
INNER JOIN HRR30240 M ON M2.EMPLOYID=M.EMPID_I
INNER JOIN UPR40301 JOBS ON JOBS.JOBTITLE=M2.JOBTITLE
INNER JOIN UPR40300 DEPTS ON DEPTS.DEPRTMNT=M2.DEPRTMNT
INNER JOIN UPR00100 BINFO ON BINFO.EMPLOYID=M2.EMPLOYID
LEFT OUTER JOIN UPR41700 SVISOR ON SVISOR.SUPERVISORCODE_I=BINFO.SUPERVISORCODE_I
LEFT OUTER JOIN HRR30240 SVISOR_D ON SVISOR.EMPLOYID=SVISOR_D.EMPID_I
WHERE M2.EMPLOYID=@EMP_ID
AND M2.INACTIVE=0
ORDER BY M2.EMPLOYID,DEPTS.DSCRIPTN, JOBS.DSCRIPTN;
END;
GO
-----------------------------------------
Regards,
Muhammad Shafee |
|
| Back to top |
|
|
| sasad |
| Posted: Tue Jun 17, 2014 4:39 am Post subject: |
|
|
Acolyte
Joined: 30 Apr 2014
Posts: 67
|
I have just found out that issue is because of local variables in SQL SERVER SP. If I remove all the local variables "DECLARE
@ANNUAL_LEAVES INT , @HAJLEAVE INT ,@GRADE_YEAR_BLNC INT " from SP it just ran fine. Please advise!! |
|
| Back to top |
|
|
| jlaisbett |
| Posted: Tue Jun 17, 2014 1:21 pm Post subject: |
|
|
Apprentice
Joined: 27 Nov 2009
Posts: 39
|
For SQL server you need SET NOCOUNT ON; within your stored procedure if not set on the database by default.
The behaviour you are seeing is what normally occurs when that's not set. |
|
| Back to top |
|
|
| sasad |
| Posted: Wed Jun 18, 2014 11:59 pm Post subject: |
|
|
Acolyte
Joined: 30 Apr 2014
Posts: 67
|
Thanks jlaisbett.
it works like a charm   |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
