I have asked my programmers to work on a function to work with unknown stored procedures. The Software Manager (me) often makes a quick T-SQL statement to retrieve data, requested by the customer. This can be a simple inner join or a more complicated function with unions and temp-tables. The Software Manager can create that on the spot, whilst this does not need the customer to wait for the next release of the software (especially when the software is developed for multiple customers, who do not have the same reporting needs).
The customer has to pay for the time the Software Manager spend on making this function, but every time they need this report again, they need to pay for the same time again. So my solution is for the Software Manager to place this query in a stored procedure, allowing the customer to run this report upon request and export it to CSV for further handling or printing in Excel.
I found these SQL statements to assist with this:
SELECT ALL QUALIFYING STORED PROCEDURES
select right(SO.name,len(SO.name)-5) Rapportnaam , SO.object_id RapportID
FROM sys.objects AS SO
where SO.name like ‘IVVR_%’
ORDER BY SO.name
I’d like the customer to select the query they need. With this, we will present them the parameters they may fill.
SELECT PARAMETERS AND FIELD TYPES
declare @idnr as integer=9999999999
SELECT
P.parameter_id AS [ParameterID]
,P.name AS [ParameterName]
,TYPE_NAME(P.user_type_id) AS [ParameterDataType]
,P.max_length AS [ParameterMaxBytes]
,P.is_output AS [IsOutPutParameter]
FROM sys.parameters AS P
where P.OBJECT_ID=@idnr
ORDER BY P.parameter_id
I myself have an Delphi history, and have created a proof of concept, where I use a stringgrid to display the parameters and validate the input in the stringgrid with the returned type. The resulting data is taken field by field and is exported to a CSV file. The field-type is used each time to determine if embedding quotes are needed or not.
I have given these SQL’s and my proof-of-concept to my Clarion programmer, and he states that this is not possible in Clarion, as we do not know the amount of fields that will be returned. He states that any SQL result has first be put in a queue, before it can be handled. He can make 20 string-fields, where the results with 1 upto 20 fields can be placed, but when a stored procedure is made with 21 fields, it will stop working.
Is there an easier way in Clarion to retrieve variable data and export it straight to CSV? If needs be, the Software Manager can be forced to place embedding-quotes already in the statement, if that would help.