CODEWITHSUNDEEP
sql-serverpowerbuilder
veedsql
veedsql

Reputation: 5

must declare scalar variable error in Powerbuilder

I'm facing this error " must declare the scalar variable @return " in PowerBuilder 9 running on SQL server 14. When I'm executing the stored procedure using the SQL management studio it is returning 10000 as expected. But while calling this SP from PowerBuilder I'm facing the error. Any suggestions are appreciated. Thanks

Function in PowerBuilder code: 
 Declare sp_v procedure for
 @return = proc_v_sp
 @eid = :p_eid,
 @year = :p_year,
 @bid = :p_bid,
 @hid = :p_hid
 using sqlca;
 Execute sp_v;
 IF SQLCA.SQLCode <> 0 THEN
 lReturn = SQLCA.SQLCode
 ELSE
 FETCH sp_v INTO :lReturn;
 END IF
 CLOSE sp_v;

 In SQL SERVER SP:
 Alter procedure proc_v_sp
 @eid int,
 @year int,
 @bid varchar(8),
 @hid char(3)
  As 
    Begin
    Declare @count int,
    Declare..............
    ..........ignoring as it is long SP...........
    Select @count = count(*)
    from sy_e
    where sy_e_eid = @eid and sy_e_year= @year
    IF @count >0
    RETURN 20000
    ELSE 
      RETURN 10000
    END

Upvotes: 0

Views: 1155

Answers (3)

NoazDad
NoazDad

Reputation: 620

I don't have sample code.
But it's super easy... Your SP needs to return a result set instead of a return value - even if that result set is a single value on a single row.

Change the RETURN to SELECT. That returns a result set.

Now, create a datawindow and select Stored Procedure as the datasource. Then select your sp as the source. Test it by providing the values for arguments and seeing if it returns the result you're looking for.

From here, it's just PB code. 

datastore myDW  
myDW = create datastore  
myDW.setTransObject( SQLCA )  
myDW.retrieve( args... )  
theResult = myDW.getItemNumber( 1, "return_status" )

Upvotes: 0

NoazDad
NoazDad

Reputation: 620

You're making this all way too hard...

Change your RETURN to SELECT, and use a stored procedure datawindow. You can then rip out all that code, and replace it with one line.

dw.retrieve( args )

And your return value will be dw.getItemNumber( 1, "return_status")

-Paul-

Upvotes: 0

Mark Sponsler
Mark Sponsler

Reputation: 101

Looking at the documentation, I don't see anything about how to get a RETURN value back from an executed stored procedure. The documentation lists the syntax for the SP declaration in PB as...

DECLARE logical_procedure_name PROCEDURE FOR
    SQL_Server_procedure_name
    @Param1 = value1, @Param2 = value2, 
    @Param3 = value3 OUTPUT,
    {USING transaction_object} ;

So that's why you're getting the syntax error. It's just not expecting @return there. If you can change the stored procedure, then you should be able to use an OUTPUT parameter. After a bit of googling, it looks like you would still have to use FETCH after the EXECUTE to get the variable specified for the output parameter populated.

I came back to this once I had PB in front of me because I was curious if it was possibles. After a bit of experimentation and looking at the MSDN docs for RETURN, I was able to get the return value populated in the returnValue variable using the code below.

long returnValue
DECLARE sp_test PROCEDURE FOR
    @return_status = sp_test_return
USING SQLCA;

EXECUTE sp_test;
FETCH sp_test INTO :returnValue;

Here's the stored procedure.

CREATE PROCEDURE [dbo].[sp_test_return]
AS
    RETURN 159
GO

Upvotes: 0

Related Questions