Calling MSSQL stored procedure with OUTPUT procedure from clarion

[sk2107c]

Dear All

I use my own SQL stored procedures using the CALL command sending the parameters from clarion and getting results and everything was fine.

now we have a third party stored procedure with a lot of parameters and calling the stored procedure using the variable definition.

this a simple example to clear my case:

create PROCEDURE sk_test5 @aa nvarchar(30)=NULL
, @bb nvarchar(30)=NULL
,@result nvarchar(30) = NULL OUTPUT
AS
select @result =    @aa + @bb
GO

To execute from sql

@Result and @myResult should be nvarchar(60) (declaration is not saved in this note.. dont know why)

exec sk_test5 @bb='--Para2--',@aa='--Para1--',@Result = @myresult OUTPUT
select @myresult AS Result

so the parameters are not sent in order, what I need is to get the OUTPUT parameter using this method of sending parameters from Clarion.

thank you all in advance.

Regards

 

[oxidadus]

Hi there,

Usually i use a Dummy table and put result from SP in dummy buffer and then use dummy buffer as a table.

create PROCEDURE sk_test5 @aa nvarchar(30), @bb nvarchar(30)
AS
   select  @aa + @bb
GO

DUMMY{prop:sql} = 'EXEC sk_test5 ''foo'',''bar'''
IF ERRORCODE() THEN
   MESSAGE(ERROR() & ' ' & FILEERROR())
ELSE
    NEXT(DUMMY)
    IF NOT ERRORCODE() THEN 
          MESSAGE(DUM:FIELD01)
    END
END

 

[sk2107c]

Hi,
thank you for your reply.
may be my idea was not clear, what I need is sending the parameters using this exact method defined by @variables

exec sk_test5 @bb='--Para2--',@aa='--Para1--',@Result = @myresult OUTPUT

without having to send the parameters in sequence or all of them.

the actual stored procedure I have contains around 25 parameters and from some application I have to pass is only 3. this stored procedure is used by different applications, so each application is sending only what is required and no need to send all.
so now I have to put commas to cover all the parameters, but with this method in SQL I can pass only the parameters which I need and it doesn't have to be in sequence.

It works fine in SQL Management studio but I don't how to apply in Clarion, so currently I am using the method you suggested until I can figure how to apply the other method.

Thank you any way and Best Regards.

 

[oxidadus]

Well,

The other method is described at

http://www.tek-tips.com/viewthread.cfm?qid=621975

Best Regards

 

[sk2107c]

thank you oxidadus and I really appreciate the followup.

I checked that but still it is the same first method.

the difference between the first and the second is that in the second method I don't have to pass the the parameters in same sequence of the stored procedure.

so if I execute the stored procedure as

exec sk_test5 @bb='--Para2--',@aa='--Para1--',@Result = @myresult OUTPUT

or

exec sk_test5 @aa='--Para1--',@Result = @myresult OUTPUT,@bb='--Para2--'

the result will be the same (OK) because the receiving parameters receive their equivalent by the name of the variable and not by sequence as in the first method which is the behavior of functions in all languages I know.

what I mean is the parameter @aa will be received by the variable @aa inside the stored procedure regardless to its location and so on.

thanks again

 

[oxidadus]

Now i think i understand what you wanna do, the way that you call stored procedures binding parameters as @bb = '& loc:SomeValue &','& @aa = '& loc:SomeValue2 lets you call SP and I don't have to pass the parameters in same sequence of the stored procedure. @my_result is the variable that you have to BIND(), or just SELECT it in your SP to treat it as a value un DUMMY Buffer.

 

[sk2107c]

Thanks. I will do some tests and infom you

Best regards