Focal Point
[Solved] MS SQL Stored Proc with passed paramaters

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3557033686

May 04, 2017, 03:30 PM
gregv
[Solved] MS SQL Stored Proc with passed paramaters
After searching and reading many posts with no clear answer, I write to the pros in this forum:

Can I pass parameters to an MS SQL stored procedure when using a synonym to read the data or do I have to use SQL Pass-thru?

If I can, how is it done. I've tried using a WHERE and IF clause on my TABLE FILE along with setting defaults in the master and in my procedure and changing the spelling of the parameters and variables many different ways but with no success.

Any help is greatly appreciated.

As always, tia.

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



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 04, 2017, 04:01 PM
BabakNYC
According to the doc:

TABLE FILE synonym 
PRINT [parameter [parameter] ... | *]
[IF in-parameter EQ value]
  .
  .
  .
END

where:

synonym

    Is the synonym of the stored procedure you want to execute.
parameter

    Is the name of a parameter whose values you want to display in the report. You can specify input parameters, output parameters, or input and output parameters.

    If the stored procedure does not require parameters, specify an asterisk (*). This displays a dummy segment, created when the synonym is generated, to satisfy the structure of the SELECT statement.
*

    Indicates that you want to display all indicated parameters, or that there are no required parameters. 
IF

    Is an IF or WHERE keyword. Use this to pass a value to an IN parameter or an INOUT parameter in IN mode.
in-parameter

    Is the name of an IN parameter, or INOUT parameter in IN mode, to which you want to pass a value.

    Note: The length of in-parameters cannot exceed 1000 characters if the adapter is configured for Unicode support.
value

    Is the value you are passing to a parameter.



WebFOCUS 8206, Unix, Windows
May 04, 2017, 04:55 PM
BabakNYC
Here's a synonym I created for an sp in Adventurworks.

FILENAME=USPGETMANAGEREMPLOYEES, SUFFIX=SQLMSS  , $
  SEGMENT=INPUT, SEGTYPE=S0, $
    FIELDNAME=@BUSINESSENTITYID, ALIAS=P0001, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
  SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
    FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $
  SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
    FIELDNAME=RECURSIONLEVEL, ALIAS=RecursionLevel, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ORGANIZATIONNODE, ALIAS=OrganizationNode, USAGE=A4000, ACTUAL=A4000,
      MISSING=ON, $
    FIELDNAME=MANAGERFIRSTNAME, ALIAS=ManagerFirstName, USAGE=A50, ACTUAL=A50, $
    FIELDNAME=MANAGERLASTNAME, ALIAS=ManagerLastName, USAGE=A50, ACTUAL=A50, $
    FIELDNAME=BUSINESSENTITYID, ALIAS=BusinessEntityID, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=FIRSTNAME, ALIAS=FirstName, USAGE=A50, ACTUAL=A50,
      MISSING=ON, $
    FIELDNAME=LASTNAME, ALIAS=LastName, USAGE=A50, ACTUAL=A50,
      MISSING=ON, $


Here's a report you can run against it:

ENGINE INT CACHE SET ON


-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE ibisamp/uspgetmanageremployees
SUM USPGETMANAGEREMPLOYEES.ANSWERSET1.RECURSIONLEVEL
USPGETMANAGEREMPLOYEES.ANSWERSET1.BUSINESSENTITYID
BY USPGETMANAGEREMPLOYEES.ANSWERSET1.MANAGERFIRSTNAME
BY USPGETMANAGEREMPLOYEES.ANSWERSET1.MANAGERLASTNAME
BY USPGETMANAGEREMPLOYEES.ANSWERSET1.FIRSTNAME
WHERE USPGETMANAGEREMPLOYEES.INPUT.@BUSINESSENTITYID EQ &BUSINESSENTITYID.(<1,1>, <2,2>, <3,3> |FORMAT=I11).@BUSINESSENTITYID:.;
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, HFREEZE=OFF, $
ENDSTYLE
END

-RUN





WebFOCUS 8206, Unix, Windows
May 04, 2017, 04:57 PM
gregv
BabakNYC, I tried that. No joy.

No matter how I spell the synonym field, @StartDate, &StartDate or StartDate or with all caps or all lowerecase, I still can not get the proc to run.

It throws an error something like: @StartDate procedure parameter not passed.

Thanks.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 04, 2017, 04:59 PM
gregv
Ok. this is with a WHERE which I also tried but I did not try prompting. That maybe next.

Thanks.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 04, 2017, 05:02 PM
BabakNYC
This works too:

WHERE USPGETMANAGEREMPLOYEES.INPUT.@BUSINESSENTITYID EQ 1;


Can you post the synonym or is it too long?  



WebFOCUS 8206, Unix, Windows
May 05, 2017, 12:06 PM
gregv
Here's the procedure, the master, the error, and CHECK FILE.
Thanks for looking!

 -DEFAULTH &StartDate=20170301
 -DEFAULTH &EndDate=20170331
 TABLE FILE FLASHRPTAPPOINTMENTS
 SUM
 CNT.FLASHRPTAPPOINTMENTS.ANSWERSET1.APPOINTMENT_ID
 BY	FLASHRPTAPPOINTMENTS.ANSWERSET1.GENDER_NM
 WHERE FLASHRPTAPPOINTMENTS.INPUT.@StartDate GE 20170301;
 WHERE READLIMIT IS 10
 END
 FILENAME=  FLASHRPTAPPOINTMENTS
 @StartDate         P0001              I11
 @EndDate           P0002              I11
 @RETURN_VALUE      P0000              I11
 APPOINTMENT_ID     APPOINTMENT_ID     P20
 APPT_DATE_DIM_ID   APPT_DATE_DIM_ID   P20
 PATIENT_DIM_ID     PATIENT_DIM_ID     P20
 RACE1_NM           RACE1_NM           A256V
 ETHNICITY_NM       ETHNICITY_NM       A256V
 GENDER_NM          GENDER_NM          A10V
 RESOURCE           Resource           A160V
 PCPATAPPT          PCPatAppt          A160V
 APPTTYPE           ApptType           A50V
 IS_APPT_KEPT       IS_APPT_KEPT       I11
 IS_SAMEDAY         IS_SAMEDAY         I11
 IS_MORNING         IS_MORNING         I11
 IS_AFTERNOON       IS_AFTERNOON       I11
 IS_EVENING         IS_EVENING         I11
 IS_DAY             IS_DAY             I11
 IS_WEEKEND_HOLIDAY IS_WEEKEND_HOLIDAY I11
 IS_NO_SHOW         IS_NO_SHOW         I11
 IS_CANCELLED       IS_CANCELLED       I11
 CANX_RSN_TXT       CANX_RSN_TXT       A256V
 (FOC1400) SQLCODE IS 201 (HEX: 000000C9) XOPEN: 42000
 (FOC1500)  :  Microsoft OLE DB Provider for SQL Server: [42000] Procedure or function
 (FOC1500)  :  'FlashRptAppointments' expects parameter '@StartDate', which was not
 (FOC1500)  :  supplied.
 L    (FOC1406) SQL OPEN CURSOR ERROR.


 0 NUMBER OF ERRORS=     0
 NUMBER OF SEGMENTS=   3  ( REAL=    3  VIRTUAL=   0 )
 NUMBER OF FIELDS=    22  INDEXES=   0  FILES=     1
 TOTAL LENGTH OF ALL FIELDS= 1240




Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 12:13 PM
BabakNYC
You don't have a value for @EndDate. All input parameters need to have a value.

If you open the master file on the reporting server console, click Sample Data, you'll probably get prompted for both @StartDate and @EndDate. Unless you put a value into both parameters, you'll get the same error.

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


WebFOCUS 8206, Unix, Windows
May 05, 2017, 12:50 PM
gregv
Same results. Thanks.

TABLE FILE FLASHRPTAPPOINTMENTS
 SUM
 CNT.FLASHRPTAPPOINTMENTS.ANSWERSET1.APPOINTMENT_ID
 BY	FLASHRPTAPPOINTMENTS.ANSWERSET1.GENDER_NM
 WHERE FLASHRPTAPPOINTMENTS.INPUT.@StartDate GE 20170301;
 WHERE FLASHRPTAPPOINTMENTS.INPUT.@EndDate LE 20170331;
 WHERE READLIMIT IS 10
 END

(FOC1400) SQLCODE IS 201 (HEX: 000000C9) XOPEN: 42000
 (FOC1500)  :  Microsoft OLE DB Provider for SQL Server: [42000] Procedure or function
 (FOC1500)  :  'FlashRptAppointments' expects parameter '@StartDate', which was not
 (FOC1500)  :  supplied.
 L    (FOC1406) SQL OPEN CURSOR ERROR.




Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 01:12 PM
BabakNYC
Could you try this code?

TABLE FILE FLASHRPTAPPOINTMENTS
 SUM
 CNT.FLASHRPTAPPOINTMENTS.ANSWERSET1.APPOINTMENT_ID
 BY	FLASHRPTAPPOINTMENTS.ANSWERSET1.GENDER_NM
 WHERE P0001 GE 20170301;
 WHERE P0002 LE 20170331;
 WHERE READLIMIT IS 10
 END




WebFOCUS 8206, Unix, Windows
May 05, 2017, 01:44 PM
gregv
quote:
TABLE FILE FLASHRPTAPPOINTMENTS
SUM
CNT.FLASHRPTAPPOINTMENTS.ANSWERSET1.APPOINTMENT_ID
BY FLASHRPTAPPOINTMENTS.ANSWERSET1.GENDER_NM
WHERE P0001 GE 20170301;
WHERE P0002 LE 20170331;
WHERE READLIMIT IS 10
END


Same outcome.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 01:47 PM
BabakNYC
Can you get sample output from the synonym?
Are there other SP's you could test?


WebFOCUS 8206, Unix, Windows
May 05, 2017, 01:53 PM
gregv
same error for sample out.
I'll try another sp.
Thanks.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 02:20 PM
gregv
Update:
If I run the 'Sample Data', for this same master, from App Studio I get the same error BUT if I run it from the Reporting Server Console I get records while passing @StartDate and @EndDate!

Any ideas why the difference?

Thanks.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 02:27 PM
BabakNYC
Could be a duplicate master file that's out of date. It could also be an access file (.acx) that's pointing to the wrong connection.

What version/gen of WebFOCUS, Reporting Server and App Studio are you running?

Can you create a new synonym, choose a different name for the master file that's unique (try abcxyz.mas) and then test it again?


WebFOCUS 8206, Unix, Windows
May 05, 2017, 02:42 PM
Don Garland
quote:
FILENAME= FLASHRPTAPPOINTMENTS
@StartDate P0001 I11
@EndDate P0002 I11
@RETURN_VALUE P0000 I11
APPOINTMENT_ID APPOINTMENT_ID P20
APPT_DATE_DIM_ID APPT_DATE_DIM_ID P20
PATIENT_DIM_ID PATIENT_DIM_ID P20
RACE1_NM RACE1_NM A256V

quote:
FILENAME= FLASHRPTAPPOINTMENTS
@StartDate P0001 I11
@EndDate P0002 I11
@RETURN_VALUE P0000 I11
APPOINTMENT_ID APPOINTMENT_ID P20
APPT_DATE_DIM_ID APPT_DATE_DIM_ID P20
PATIENT_DIM_ID PATIENT_DIM_ID P20
RACE1_NM RACE1_NM A256V
ETHNICITY_NM ETHNICITY_NM A256V



How was this synonym created?


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
May 05, 2017, 02:42 PM
gregv
new master, same results:
I can see output with 'sample data' but I get the parameter error when I run a procedure, both in RS console.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 02:44 PM
BabakNYC
Same version/Gen of WebFOCUS, Reporting Server and App Studio?

I'd say you ought to call IB tech support.


WebFOCUS 8206, Unix, Windows
May 05, 2017, 03:15 PM
gregv
Don Garland,
I used RS console.

BabakNYC: I'm not sure versions are the issue since, from RS, there is 'sample' data but the parameter error from a focexec.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 03:16 PM
Michael L Meagher
Could it be as simple as needing quotes around the date? I have a similar master made from a stored procedure and use a very similar WHERE statement to yours.

FILENAME=CCSGDETAIL2AE_WF, SUFFIX=SQLMSS  , $
  SEGMENT=INPUT, SEGTYPE=S0, $
    FIELDNAME=@RUNID, ALIAS=P0001, USAGE=A38, ACTUAL=A38,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
  SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
    FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $
  SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
    FIELDNAME=FY, ALIAS=FY, USAGE=A4, ACTUAL=A4,
      MISSING=ON, $
    FIELDNAME=RUN_PARM, ALIAS=RUN_PARM, USAGE=A8, ACTUAL=A8,
      MISSING=ON, $
    FIELDNAME=HIGH_GROUP_PARM, ALIAS=HIGH_GROUP_PARM, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=GROUP_PARM, ALIAS=GROUP_PARM, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=GROUP_ORDER, ALIAS=GROUP_ORDER, USAGE=A100, ACTUAL=A100,
      MISSING=ON, $


 
-DEFAULTH &RunID = '{0CCBB6C0-DCC0-4A26-AB7F-1C39241E7E3E}';
TABLE FILE CCSGDETAIL2AE_WF
BY  @RUNID
BY  FY
BY  RUN_PARM
BY  HIGH_GROUP_PARM
BY  GROUP_PARM
BY  GROUP_ORDER
WHERE @RUNID EQ '&RunID';
END



WebFOCUS 8.2.03 - Production
WebFOCUS 8.2.04 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980
May 05, 2017, 03:52 PM
gregv
Michael, your USAGE for @RUNID is alpha, mine is integer for @StartDate.
I tried it anyway but with no luck.
Thanks.



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 05, 2017, 04:32 PM
BabakNYC
Greg:
Not that it should matter, but why are @StartDate and @EndDate Integers? I just created a master for SalesbyYear in Adventureworks and the parms have a format of HYYMDs. I'm wondering if that has something to do with it.


WebFOCUS 8206, Unix, Windows
May 05, 2017, 07:25 PM
Michael L Meagher
quote:
Update: If I run the 'Sample Data', for this same master, from App Studio I get the same error BUT if I run it from the Reporting Server Console I get records while passing @StartDate and @EndDate!Any ideas why the difference?


Does the client have the same access rights as the reporting server?


WebFOCUS 8.2.03 - Production
WebFOCUS 8.2.04 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980
May 06, 2017, 10:33 PM
Don Garland
Does it matter that there is only one return value in the master file?


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
May 08, 2017, 05:58 AM
Wep5622
quote:
Originally posted by gregv:
Same results. Thanks.

TABLE FILE FLASHRPTAPPOINTMENTS
 SUM
 CNT.FLASHRPTAPPOINTMENTS.ANSWERSET1.APPOINTMENT_ID
 BY	FLASHRPTAPPOINTMENTS.ANSWERSET1.GENDER_NM
 WHERE FLASHRPTAPPOINTMENTS.INPUT.@StartDate GE 20170301;
 WHERE FLASHRPTAPPOINTMENTS.INPUT.@EndDate LE 20170331;
 WHERE READLIMIT IS 10
 END

(FOC1400) SQLCODE IS 201 (HEX: 000000C9) XOPEN: 42000
 (FOC1500)  :  Microsoft OLE DB Provider for SQL Server: [42000] Procedure or function
 (FOC1500)  :  'FlashRptAppointments' expects parameter '@StartDate', which was not
 (FOC1500)  :  supplied.
 L    (FOC1406) SQL OPEN CURSOR ERROR.


Does it work with EQ instead of GE/LE? I imagine it might be a bit difficult to translate those statements to function parameters... Actually, I'm pretty sure that's your problem.

If that's not it, I'm a bit suspicious about that INPUT segment in your condition. Have you tried with just:
 WHERE @StartDate GE 20170301;
 WHERE @EndDate LE 20170331;



WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
May 08, 2017, 11:56 AM
eric.woerle
It doesn't make sense to use GE/LE with a stored procedure. The Where statements are just passing parameters. The command for the stored procedure will not pass the operator. If you need the Dates to work as a between, that has to be within the stored procedure. You can't control that at the where statement.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
May 08, 2017, 12:10 PM
gregv
Wep522: Surprise to me, it works with EQ but not GE or LE!
When I compare the record count for these 2 scenarios they don't match, meaning I have to figure out another way to do a range of start_date and end_date.

Thanks all for your time and suggestions!



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
May 08, 2017, 12:15 PM
gregv
Got it:
No need to rewrite the fex since the GE and LE are built into the stored Procedure!
I just need to pass the values for start_date and end_date!



Greg



current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8