Hello,

Is there a way to set a stored procedure's parameter variable to the userid? I seem to be having difficulties doing this. Thanks

Here's the code - '&ppnum' is the stored proc variable

 -* File pulse18.fex

ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.PULSE18Agent '&ppnum'

;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END

DEFINE FILE SQLOUT
PPNUME/A7=(EmpID);
USERID/A8='&IBIC_user';
USERID9/A8=(USERID);
USERNUM/A6=EDIT(USERID9, '$$999999');
USERID2/A8=UPCASE(7,USERID9,'A7');
END

SET NODATA = ' '

SET &ppnum = USERID2

TABLE FILE SQLOUT
PRINT
     EmpID
     FName
     LName
     Division
     Team
     Role
     TotRank
     COMPUTE Rank/D6.1% = TotPerRank * 100;
     TotPoints
     COMPUTE Productivity/D6.1% = Prod * 100;
     ProdRank
     COMPUTE Rank/D6.1% = ProdPerRank * 100;
     ProdPoints
     RCpH
     RCpHRank
     COMPUTE Rank/D6.1% = RCpHPerRank * 100;
     RCpHPoints
     UpH
     UpHRank
     COMPUTE Rank/D6.1% = UpHPerRank * 100;
     UpHPoints
     CIQ
     CIQRank
     COMPUTE Rank/D6.1% = CIQPerRank * 100;
     CIQPoints
     MonScore
     MonRank
     COMPUTE Rank/D6.1% = MonPerRank * 100;
     MonPoints
HEADING
"Pulse - Core Sales"
" "
FOOTING BOTTOM
"Report Date: <+0>&DATE <+0> "
"Created By the Reporting Group"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
END 

This message has been edited. Last edited by: Kerry,

IBIC_user is not passed as a standard variable. You would have to do something special (for instance in the site.wfs) to get this done. Is there some method in place to pass the variable?
And by the way, SET &ppnum=USERID2 is not a valid statement. Don't know exactly what you are trying to accomplish with that...

I think you had best do the username thing first, using dialog manager, and then do the call to the stored procedure - something like:

-SET &ppnum = UPCASE(8,&IBIC_user,'A8');

ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.PULSE18Agent '&ppnum'
;

Are you trying to get USERID2 into &ppnum ?

Yes - I've need to get the defined field USERID2 into &ppnum which is the stored procs parameter. I'll try Gam's suggestion. I'll get back to you guys. Thanks!

You cannot pass a field value directly into a dialogue manager variable. You would have to retrieve the value into a file and then -READ it -

TABLE FILE parmfile
PRINT USERID2
WHERE [your predicate]
ON TABLE SAVE AS SPPARM
END
-RUN
-READ SPPARM, &ppnum

Notes:

• Use SAVE and not HOLD - default format is ALPHA and you do not get a .mas which you do not need.
• Usage of the comma after the saved file name in the -READ statement negates the need to specify variable length.
• This will, of course, only read the first value in the saved file.
• The -RUN is necessary to ensure that the table request is actioned prior to the DM code for reading it.

T

The other way to get a field value into a variable is to create the set statement in the TABLE FILE, and -INCLUDE the save file.

TABLE FILE CAR
 PRINT COMPUTE SET_LINE/A80 = '-SET &|MyVal = ''' || COUNTRY || ''';' ;
 BY COUNTRY NOPRINT
 WHERE COUNTRY EQ 'ENGLAND'
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SAVE AS INC_SET
END
-RUN
-INCLUDE INC_SET
-TYPE &MyVal

There are many feline pelts around at the moment

T

That there is, all depends on which cat you get it from.