I have created Functions in MS SQL that return values. You call them like this:
select fn_test 'param1','param2' Specificy a return field for $1$. For a Stored Procedure, to obtain the return result, which I am assuming will tell you whether or not the procedure succeeded or failed, put a select statement directly before the " return 1" statement. Select the value you would like returned. Then you call the proc from like this: exec sp_testproc 'param1' It might not be the correct way but it seems to work On Thu, Dec 10, 2009 at 1:14 PM, David Morgan <[email protected]>wrote: > Hi Conny/Joe > > Not sure if it helps but MS SQL doesn't require any helper table so where > in Oracle you might use:- > > Select sysdate from dual ; > > In MS-SQL you could use > > select getsysdate > > Perhaps you can do something similar with your SP? > > Select sp > > > Failing that - you could use a sledgehammer to crack the nut and use a Java > JDBC program to launch the SP and write out the result (ie use Java the Java > program to "glue" the two together?) > > ARS <-- Java program --> JDBC <-- Database (Oracle/SQL > SERVER/SYBASE/INFORMIX etc) > > Regards > Dave Morgan > > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [email protected]] On Behalf Of Joe D'Souza > Sent: 10 December 2009 11:05 > To: [email protected] > Subject: Re: Get result from stored procedure > > 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]<sponsor%[email protected]>ARSlist: > "Where the Answers Are" > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum > Sponsor:[email protected]<sponsor%[email protected]>ARSlist: > "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"

