In the documentation for SAS BI WebServices for XMLA (http://support.sas.com/documentation/cdl/en/wbsvcdg/61496/HTML/default/sample.htm) there is a good explanation of how to write a stored process for an XMLA web service. But we are looking to expose the sproc as a Generated Web Service (the new way in 9.2). I am looking to understand how to write the sproc if its going to be exposed as a "Generated web service".
I also searched in the "samples" database but had no luck. Did anyone try to expose a stored process as a Generated web service? If so, can you share some sample code? Essentially, I would like to understand what magic needs to be done in the sproc to read the input parameters and send the output parameters.
SAS Stored Process input parameters are surfaced to your SAS session as GLOBAL SAS Macro variables. So, your stored process needs to use SAS Macro variables in an appropriate manner.
For example, &TABLENAME in the code you show is a Macro variable, whose value will be supplied either from a %LET statement in the Stored Process code; from a CALL SYMPUT in the Stored Process code or from an INPUT parameter value for which the user is prompted in one of the prompting interfaces in either a client application or from a default which is set in the stored process parameter registration process.
Quoted from above page:
" The following list explains unique details about Web service stored processes:
-- The data returned by the stored process must be XML.
--The %STPBEGIN or %STPEND macros are not used with Web service stored processes. These macros set up ODS statements for the stored process, but Web services do not use ODS.
--Web service stored processes produce streaming results, which means that the SAS program writes output to _WEBOUT, typically by using the following LIBNAME statement: libname _WEBOUT xml xmlmeta=&_XMLSCHEMA;
-- The _XMLSCHEMA macro is unique to Web services. This macro is passed to the SAS program when it is invoked from the Web service. The _XMLSCHEMA macro is set to one of three values depending on the Content property that gets passed to the Execute call.
-- Input streams are unique to Web services. Input streams are filerefs that contain XML. "
So in a sense, in order to understand how to write a stored process for a generated web service you need to:
1) understand some of SAS Macro facility basics (the paper is a good resource: http://www2.sas.com/proceedings/sugi28/056-28.pdf )
2) understand the basics of how macro variables will contain the stored process input parameter values
3) understand how to write a "regular" stored process (perhaps test your stored process in a client application like EG)
4) then apply the changes quoted above to your code for the web services stored process usage. (Such as adding &_XMLSCHEMA and using _webout appropriately and making sure that the results are streamed back to the web service.)