2 replies Latest Post - ‏2012-10-31T06:30:52Z by HSSH_Sandip_Kulkarni
13 Posts

Pinned topic Dynamic SQL with stored procedure

‏2012-10-09T10:07:33Z |
I have a grid that populates data from a stored procedure that fetches records from 3 to 4 tables. Since the stored procedure query would ideally return about 10000 records, I intend to use "PagedXML Retriver" to retrieve data pagewise.

I intend to incorporate the below functionality to the grid
Sorting on all columns. Now the sorting is intended to hit the DB every time on click of "Coloum sort". Is there a way to make my Stored procedure dynamically do this. This was possible if I used a select query in the SQL call builder instead of a procedure.

  • How do I force a DB call on click of Column Sort in the data grid
  • How could I fetch the column name and ascending /descending values to pass it to the stored proc if my Stored proc is able to return sorted values based on these input parameters.

I intend to use Service Provider and Service Consumer approach for implementing the same. SQL call in the provider model with DCM at the consumer end would take care of formatting, labelling and a data page to display the table grid.On Column sort click, need to make a call to the provider model passing the column name and ascending/descending option.
Updated on 2012-10-31T06:30:52Z at 2012-10-31T06:30:52Z by HSSH_Sandip_Kulkarni
  • DGawron
    50 Posts

    Re: Dynamic SQL with stored procedure

    ‏2012-10-11T13:12:59Z  in response to HSSH_Sandip_Kulkarni
    Search in the WEF Wiki for samples and articles that talk about writing a "custom data retriever". A custom data retriever uses an API provided by WEF for customers to create their own back-end paging solutions. The passing of sort column and direction into the retriever is automated by WEF and part of the API. Your job is implementing the methods of the retriever API as STP executions and returning the correct set of IXml data. The retriever will need to pass the sort column and direction as STP input parameters along with whatever other data is needed to execute the queries in the STP.

    If you have control over how the STP is written then you can make the paging implementation even more efficient by also passing in paging parameters (start page and number of rows, first row and last row) that can be used by the queries in the STP to return only those rows needed for display.
  • HSSH_Sandip_Kulkarni
    13 Posts

    Re: Dynamic SQL with stored procedure

    ‏2012-10-31T06:30:52Z  in response to HSSH_Sandip_Kulkarni
    While I have not had the time to implement this, I believe I have the solution. Thank you.