Hi All,

Is it possible to pass Table Valued parameter for SQL Server Functions/StoredProcedure from Webfocus? If so, Please let me know how to do it.

Thanks in advance!

This message has been edited. Last edited by: FP Mod Chuck,

Yes, you must create a master file for the Stored Procedure which is an option when creating a new Synonym using the GUI. At least in v8.09. I don't remember this in earlier versions.

Often our Call Center workers want to run a Client's Dashboard as if they are one of our Client Users. To see the Dashboard exactly the way the Client does. To do this, the Call Center worker runs a report, selects (drills down) on the Client User ID, which calls a fex with the stored procedure, passing both the Client User ID and the Call Center's WebFOCUS UserName. The SP, CLONEs the Client User security codes (cags) under the Call Center workers Dashboard profile and returns a code for either success or error.

The SP looks just like any other TABLE FILE statement, it's just the parameters must have an @ char as a prefix.


The drill down scenario if the same. Below we are passing a field value to a parameter with the same name;

TYPE=DATA,
     COLUMN=N3,
     DRILLMENUITEM='Select to replace your security profile',
          FOCEXEC=IBFS:/WFC/Repository/DivisionName/Base/User_CAGS_Clone.fex( \
     USERID=USERID \
     ),
          TARGET='_self',
$
  

The variables, passed to the stored procedure will look like the following;

TABLE FILE TEST
BY  @IBIMR_USER
BY  LOWEST @RETURN_VALUE
BY  LOWEST @USERID
WHERE ( @IBIMR_USER EQ '&IBIMR_user.EVAL' ) AND ( @USERID EQ &USERID.EVAL );
END  

The Master File will look like the following;
FILENAME=TEST, SUFFIX=SQLMSS , $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@IBIMR_USER, ALIAS=P0001, USAGE=A100V, ACTUAL=A100V,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@USERID, ALIAS=P0002, 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, $

This message has been edited. Last edited by: Don Garland,

Hi Don Garland,

What I understand from your explanation is, you are sending a single USERID to get Cloned CAGS. Correct me if I'm wrong.

In my scenario, I have a SQL Function with single parameter. The parameter is a user defined table type.

I need to pass the parameter in the form of table from webfocus. How to call the below function from webfocus?

User Defined Table

CREATE TYPE EmployeeID AS TABLE
(
Emp_Id int NOT NULL,

)

SQL Function

CREATE FUNCTION Select_Employee_Inf(@Employee [EmployeeID] ReadOnly)
AS
BEGIN

SELECT Emp_Id,
EmployeeName,
EmpSalary,
StateId,
CityId FROM dbo.Employee
WHERE
Emp_Id IN
(SELECT Emp_Id FROM @Employee)

END

I'm not sure I understand this.

You want to pass a parameter in the form of a table?

I think this means that you want to create a list of employee ids and then have your SQL Select_Employee() function use that as an IN(LIST) filter.

How are you currently creating the list of employee ids?

A littler more research and I now understand what you are trying to do. I couldn't get an array to pass through to a stored procedure.

I did borrow some code from the great WAZ, and who hasn't, and can provide this less glorious version of a single parameter using a stored procedure, similar to yours.

Here is the code based on car, that produces a parameter which is then passed through the stored procedure. I'm using a master file to connect my FEX to the procedure.

Here is a link to the code inspiration; http://forums.informationbuild...=411109644#411109644

In my version below, I left in, but commented out, WAZ's code so that you could see that it may be possible for this to work. Hopefully, someone will read through this pots and and offer up some greater insight and a solution to passing the array.

Hope this helps a little and I'm looking forward to more input on this.

Good Luck.

Here is the FEX;

SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY

-DEFAULTH &COUNTRY = '_FOC_NULL'

 
TABLE FILE CAR
-*PRINT COMPUTE FILTER_LIST/A100 = '''' || COUNTRY || ''',';
BY HIGHEST 1 COUNTRY 
ON TABLE HOLD AS A_FILTER
END
-RUN

-READFILE A_FILTER

TABLE FILE TST_PR_SELECT_COUNTRY_INF
SUM
     SEATS
     DEALER_COST
     RETAIL_COST
     SALES
BY  COUNTRY
BY  CAR
BY  MODEL
BY  BODYTYPE
-*WHERE @COUNTRY IN (
-*-INCLUDE A_FILTER
-*'9999999999999999'
-*) 

WHERE @COUNTRY EQ '&COUNTRY'
END

I modified your SQL Function a bit so that it would accept a single value
I have a CAR table in our SQL database. It's handy for testing things like this.

ALTER PROCEDURE [dbo].[PR_Select_Country_Inf]

	@country varchar(max)

AS
BEGIN
	SELECT Country
       ,Car
	   ,Model
	   ,BodyType
	   ,Seats
	   ,Dealer_Cost
	   ,Retail_Cost
	   ,Sales 
	 FROM dbo.CarTemp
	where country in (@country)

END  

I created the synonym below with the WebFOCUS GUI

FILENAME=TST_PR_SELECT_COUNTRY_INF, SUFFIX=SQLMSS  , $
  SEGMENT=INPUT, SEGTYPE=S0, $
    FIELDNAME=@COUNTRY, ALIAS=P0001, USAGE=A32761V, ACTUAL=A32761V,
      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=COUNTRY, ALIAS=Country, USAGE=A10, ACTUAL=A10,
      MISSING=ON, $
    FIELDNAME=CAR, ALIAS=Car, USAGE=A16, ACTUAL=A16,
      MISSING=ON, $
    FIELDNAME=MODEL, ALIAS=Model, USAGE=A24, ACTUAL=A24,
      MISSING=ON, $
    FIELDNAME=BODYTYPE, ALIAS=BodyType, USAGE=A12, ACTUAL=A12,
      MISSING=ON, $
    FIELDNAME=SEATS, ALIAS=Seats, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=DEALER_COST, ALIAS=Dealer_Cost, USAGE=P10.2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=RETAIL_COST, ALIAS=Retail_Cost, USAGE=P10.2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=SALES, ALIAS=Sales, USAGE=P10.2, ACTUAL=P8,
      MISSING=ON, $
  

And, all of this works pretty well, but just for one parameter.

This message has been edited. Last edited by: Don Garland,

Yes, This is the way I used to create functions/procedures and pass the variable in comma separated format.

But here we have more than 50 functions; All functions have parameters whose data types are user defined table types which are created by our client. So I need to pass the parameter in the form of table.

Because I can't ask them to change the parameter's datatype and update the query in all the functions as they are calling those functions from different platform.

I hope I have given you enough information on this. Let me know if you need more information.

Thanks Don Garland!

Where is the data that will be used to create the parameter table? If it is on the same database as the stored procedure, I'm wondering if you could use SAME_DB and create the parameter table as a #temporary table on the DBMS?

The on table statement would look something like the following;

ON TABLE HOLD AS PTABLE FORMAT SAME_DB PERSISTENCE GLOBAL_TEMPORARY

Yes, Both are in the SAME_DB. I think it's tricky to pass Table Valued parameter.So we are planning to take all the queries and do SQL passthru from WebFocus instead of calling the SQL function name.

Anyway thanks Don Garland for sharing your ideas!