There is a small difference on how you call stored procedures while using
either the direct SQL or the set field SQL feature from the ARS at least as
far as Oracle is concerned.. When using the Oracle client SQL*Plus, Execute
<procedure name> works, but from the ARS interface for SQL, EXECUTE doesn't
work. This I believe is because the ARS does not use the extended set of the
database API that SQL*Plus uses.

So in Oracle instead of using EXECUTE you use BEGIN. However no error is
returned if you use EXECUTE. It just ignores it and doesn't do anything.

I'm guessing your problem with MS-SQL is something similar.. Only I'm not
sure if Begin would be the right keyword to execute a SP if the underlying
DB is MS-SQL..

Joe

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]]on Behalf Of Conny Martin
Sent: Thursday, December 10, 2009 4:48 AM
To: [email protected]
Subject: AW: Get result from stored procedure


Joe,

I'm calling it the same way as in Query Analyzer.

declare @p_result int
exec SP_TEST 'MDP000000072713', @p_result output 
select @p_result

No Errormessage is returned but $1$ doesn't get the value from p_result

With oracle there is no problem. One can just do
select sp_test('MDP000000072713') from dual
but I don't know the correct syntax for MS-SQL.

Kind Regards Conny

-----Ursprüngliche Nachricht-----
Von: Action Request System discussion list(ARSList)
[mailto:[email protected]] Im Auftrag von Joe D'Souza
Gesendet: Donnerstag, 10. Dezember 2009 10:32
An: [email protected]
Betreff: Re: Get result from stored procedure

Conny,

Yes it is possible.. how are you calling/executing the stored procedure when
you are using it in the set-field-SQL?

Begin <procedure name>;
works from the ARS with Oracle as the backend DB.. (I'm not 100% certain if
you need the semi colon in the end)

I'm not sure if that would work with MS-SQL as well but you could try that..

Joe

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]]on Behalf Of Conny Martin
Sent: Thursday, December 10, 2009 3:08 AM
To: [email protected]
Subject: Get result from stored procedure


All,

Not sure if this is possible, but is there a way to get the results from a
stored procedure via set-field-SQL into ars.

This works in Query-Analyzer

declare @p_result int
exec SP_TEST 'MDP000000072713', @p_result output select @p_result

But in a set-fields-SQL $1$ is always null.

ARS 7.1p6
MSSQL 2005
W2K3 Enterprise Server

Any help is really appreciated.

Thanks

Conny

_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"

Reply via email to