[SOLVED] Calling Function/StoredProc with Table Valued parameter as Input Parameter
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,
WebFOCUS 8105 | 8206 Windows, All Outputs
July 20, 2017, 08:54 AM
Don Garland
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,
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
WebFOCUS 8105 | 8206 Windows, All Outputs
July 21, 2017, 02:54 PM
Don Garland
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?
July 22, 2017, 07:32 AM
Don Garland
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.
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
And, all of this works pretty well, but just for one parameter.
This message has been edited. Last edited by: Don Garland,
July 22, 2017, 04:33 PM
DineshJGoundai
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!
WebFOCUS 8105 | 8206 Windows, All Outputs
July 22, 2017, 09:02 PM
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.