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"

Reply via email to