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,

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.

Hi Kamesh,
Can you please tell me the Exact Error message you are getting?

Thanks
Richa

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.

Anybody has faced this issue.

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

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.

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

Thanks Zippo. I will try that and let you know.

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.

Cheers,

Kerry