Using Stored Procedure in Impromptu.

[susheeltips]

Hi

I am using SQL Server as the backend and "pubs" database and now i wanted to created a report by using stored procedure that fires on "pubs" database. I have used a stored procedure as mentioned in the SQL Server documentation and it is even working well in QueryAnalyser but when i give the same in Impromptu when by taking a new stored procedure report i am not getting the output.

Can someone help me .

Thanks in advance.

 

[jbecher]

Does the stored proc have input parameter(s)? I ran into the same thing with a procedure that had an input parameter. The problem had to do with the Impromptu prompt datatype being slightly different than the stored proc parameter data type.

 

[angiole]

I had to append a space to the end of the input string to the stored procedure. Don't ask me why, but all worked fine after that...

 

[oimFriendly]

Impromptu does not like stored procedures nor server side functions. It has its own database engine and it does not work well with what u r doing.

 

[griffindm]

I have used stored procedures on MS SQL server and Oracle since Impromptu version 3.5. The original API call was used only in the macro language and was kind of klunky in that it often required the use of a null string ("&quot or a zero appended to the end of passed parameters to work. When Cognos stopped allowing the API use and went to using a direct query in Impromptu I played with it for a while before giving up due to inconsistent results and limitations created by the need to have a specific catalog open when calling the report.

What I have used on both platforms for over three years in a straight forward SQL ODC call in the macro language. The syntax for MS is:

connection = SQLOpen("DSN=ODBC_DSN; UID=Username; PWD=yourpwd", outputStr, prompt:=5)
Query1 = "EXECUTE proc_name '"+sFyCd+"', "+Cstr(iPdNo)+", '"+sModBy+"', '"+sLike+"', "+Cstr(sStatus)+", '"+sInUseBy+"'"
retcode = SQLExecQuery(connection,Query1+"&quot
retcode = SQLRetrieve(connection,RetVal)
retcode = SQLClose(connection)

For Oracle the syntax is a little different:

connection = SQLOpen("DSN=ODBC_DSN; UID=Username; PWD=yourpwd", outputStr, prompt:=5)
Query1 = "BEGIN proc_name('"+FY+"',"+Cstr(Pd)+",'"+Org
Query1 = Query1+"','"+BudCode+"','"+FrcCode+"','"+ModBy$+"'); END;"
retcode = SQLExecQuery(connection,Query1+"&quot
retcode = SQLClose(connection)

In each case I do not retrieve data directly into a report, but populate a reporting table that is then called via a report in a subsequent step in the macro. I use a control table to write procedure success/failure information as the SQL ODBC options in the macro language do not support OUTPUT parameters.

There is a requirement to reduce all of the passed parameters to strings, but this has never caused me problems.

These ALWAYS work and have never given any trouble, as seen immune to Cognos' monkeying around with their direct stored procedure support.