Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to retrieve OUT parameter from SQL Server stored procedure

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to retrieve OUT parameter from SQL Server stored procedure
 Login/Join
 
Kamesh
Master
posted
Hi,

I have a stored procedure written in sql server and it returns an value through OUT parameter. When I call this procedure inside my report, it gives me error. My question here is, how to retrieve the OUT value coming from the stored procedure?

ex:

Stored procedure
------------------
name - testSP
Parameter - parm1 IN parameter
parm2 OUT parameter

FEX
----
SQL SQLMSS SET SERVER CONN
SQL SQLMSS

EX TESTSP PARM1,PARM2 ;

TABLE FILE SQLOUT
PRINT *
END
-EXIT

This message has been edited. Last edited by: Kerry,


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Darin Lee
Virtuoso
posted Hide Post
Complete documentation, including use of stored procedures and out parameters, is all described in the "Using the Adapter for Microsoft SQL Server" section of you data administrator users's manual.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Richa Kulkarni
Member
posted Hide Post
Hi Kamesh,
Can you please tell me the Exact Error message you are getting?

Thanks
Richa


Thanks,
Richa
 
Posts: 5 | Registered: September 29, 2008Report This Post
Kamesh
Master
posted Hide Post
Darin,

I have tried that way already but didn't succeed. The sample code is below

-DEFAULT &RT=0;

SQL SQLMSS SET SERVER CRS
SQL SQLMSS

EX TESTKAMESHOUTPARAM 61,? ; - throws error

EX TESTKAMESHOUTPARAM 61,?/&RT ; - gives output but return value is wrong.

EX TESTKAMESHOUTPARAM 61,? OUT ; - throws error

TABLE FILE SQLOUT
PRINT *
END

-TYPE RT VALUE IS: &RT
-EXIT

The error I am getting is,

(FOC1400) SQLCODE IS 201 (HEX: 000000C9) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Procedure 'testKameshO
: utParam' expects parameter '@RetVal', which was not supplied.
L (FOC1405) SQL PREPARE ERROR.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Kamesh
Master
posted Hide Post
Anybody has faced this issue.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Tom Flynn
Expert
posted Hide Post
Kamesh,

Your error refers to @RetVal which is not being supplied, by you, in your executable.

Here is a working example:

  
SQL SQLMSS SET SERVER CRS
SQL SQLMSS EX DATABASE.DBO.SPname '&VAR1.EVAL', '&VAR2.EVAL', '&VAR3.EVAL', 1;


In the Stored Proc, SPname is:

  
ALTER PROCEDURE [dbo].[SPname]
(

@BegDate datetime,
@EndDate datetime,
@DateType varchar(12),
@RetVal int

)


The 4 incoming values correspond to each declaration above in the proc, VAR1 = BegDate, VAR2 = EndDate, etc...
Also, notice the .EVAL...

AFTER the SP is executed, you must
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS WHATEVER
END
-RUN

Then check for records; the SP will ALWAYS return 1 record..

-IF &LINES EQ 0 GOTO NO_RECORDS;


Hope this helps...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Kamesh
Master
posted Hide Post
I am able to get the stored procedure work but my issue is, I want the output to be both the resultset and the returned value (OUT parameter value) to be passed in to webfocus.

EX TESTKAMESHOUTPARAM 61,?/&RT ;

From the above SP, I need to get the recordset and the OUT parameter value assigned to the variable &RT to use later in my fex code.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Zippo
Member
posted Hide Post
Have you tried using a synonym instead of sql passthrough. The synonym for a stored procedure provides segments for input, output and answerset.

see this documentation

http://documentation.informationbuilders.com/masterinde...adm767/dbsqlmss5.htm

this may give you what your looking for.

Zippo
 
Posts: 12 | Location: New Yawk | Registered: February 16, 2006Report This Post
Kamesh
Master
posted Hide Post
Thanks Zippo. I will try that and let you know.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Kerry
Expert
posted Hide Post
Hi Kamesh,

Here is the suggestion from internals:

He is correct it relies on synonym but that is the point. A synonym is created for the stored procedure and the output comes out as PRINT fields.

So let's say I have a stored procedure sp_Salary that excepts input NAME and returns output SALARY.

The synonym contains info about input and output so my focexecs is

TABLE FILE SP_SALARY
PRINT SALARY
IF NAME EQ ‘Kerry’
END


This returns

SALARY
100,0000

Hope this helps. Smiler

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to retrieve OUT parameter from SQL Server stored procedure

Copyright © 1996-2020 Information Builders