INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Calling MSSQL stored procedure with OUTPUT procedure from clarion

Calling MSSQL stored procedure with OUTPUT procedure from clarion

(OP)
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


RE: Calling MSSQL stored procedure with OUTPUT procedure from clarion

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 

RE: Calling MSSQL stored procedure with OUTPUT procedure from clarion

(OP)
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.

RE: Calling MSSQL stored procedure with OUTPUT procedure from clarion

(OP)
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

RE: Calling MSSQL stored procedure with OUTPUT procedure from clarion

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.

RE: Calling MSSQL stored procedure with OUTPUT procedure from clarion

(OP)
Thanks. I will do some tests and infom you

Best regards

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close