[sqr-users] Output variables with SQL Server Stored Procedures

["Bencke, Gina" <GinaBencke@forestcity.net>]

First let me say that I know there are many SQRUG postings regarding
this topic and I did read and try to apply them.  However I cannot get
SQL-Server Stored Procedures with output variables to work!

declare-variable
  float #colVariance
  text $outStr
end-declare  

move 'Hotels' to $str
execute on-error=SQL-Error
  do=Print-Columns
  @#storedProcStatus=
    fce_str_parm @entity=$str, @outStrVar=$outStr OUT
    into &ENTITY char(30), &ACTUAL float, &PROJECTED float 
    
print $outStr (+1,1)
show 'Str: <'  $outStr  '>'
  
The above code successfully prints the column values returned from the
stored procedure but $outStr is NULL and should not be. While the SQL
Analyzer code below works properly, returning a string in @var.

declare @var CHAR(30)
exec fce_str_parm 'Hotels', @var out
select @var


The SQR code below fails with an execution error " (SQR 6801)
Em2StdString: Null Operand Passed as input" regardless of how I call the
stored procedure. The SQL Analyzer code works.


move 3.0 to #actual
move 5.0 to #projected
move 0 to #colVariance

execute on-error=SQL-Error

   
  @#storedProcStatus=
     fce_calc_var @actual=#actual, @projected=#projected,
@variance=#colVariance OUT

   !fce_calc_var 3, 5, #colVariance OUT 
    
   !fce_calc_var @actual=3, @projected=5, @variance=#colVar output
     
   !fce_calc_var 3, 5, #colVar OUT 
 
  print 'Test 1'  (2,1)
  print #colVar (0, +3)  edit 999,999.99
  print #storedProcStatus (0, +3) edit 999



declare @var DECIMAL (26,3)
exec fce_calc_var 3, 5, @var out
select @var



Thanks, in advance, for your help.


Gina Bencke
Bencke Consulting Corporation

Office (440) 519-0060
email: gina.work@bencke.com

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users