Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calling MSSQL stored procedure with OUTPUT procedure from clarion

Status
Not open for further replies.

sk2107c

Programmer
May 2, 2017
22
JO
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:
Code:
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
Code:
@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


 
Hi there,

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

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

Code:
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

 
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
Code:
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.

 
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
Code:
exec sk_test5 @bb='--Para2--',@aa='--Para1--',@Result = @myresult OUTPUT
or
Code:
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

 
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.

 
Thanks. I will do some tests and infom you

Best regards
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top