4 Replies Latest reply: Jan 26, 2011 6:59 AM by Roland Kunle RSS

    Affect input values to Stored Procedure as end user

      Hi All!

      Is it possible to code something that you, as an end user, can collect data from a stored procedure using varaibles?

       

      I have now a script that passes hard coded values to a stored procedure, but I want to let the end users pass the variables to the procedure and retreive the output based on their own input. Can this be done?

       

      BR

      Robert

        • AW:Affect input values to Stored Procedure as end user

          Hi Robert,

          I can imagine the following scenario: You declare some variables and use them within the script. A user who is able to reload can change the variables via inputboxes before starting the script. Nevertheless it seems to me to be a little dangerous because the user can insert anything into the inputboxes. And having started the load with silly parameters being passed to your database ......

          HtH

          Roland

          • Affect input values to Stored Procedure as end user

            QlikView brings in all the data from a stored proc or table or view and then the user makes his selections to filter that data. This gives a lot more value than setting some parameters and then bringing in only the data for those parameters.

            Barry

            • Affect input values to Stored Procedure as end user

              @Roland: Thanks for your suggestion. I had that working, but as you say, it requires the user to be able to reload the script.

               

              @Barry: Loading all data into Qlikview would of course be optimal from that point of view. Though, the specific Stored Procedure is reffering to ther SP's and are quite large. IT would require a very complex script to reproduce all the Stored Procedures in Qlikview (even though I would prefer that if it was possible). The SP's are not very well documented andI have not written them myself.

               

              What came to my mind is whether it is possible to, when changing a variable, a macro is run to store the variable values into a text file (or similar). When the text file is updated (last changed is updated), it triggers a reload and distribute task via publisher?

               

              BR

              Robert

                • AW:Re: Affect input values to Stored Procedure as end user

                  Hello Barry,

                  thanks for your post. I agree with you when starting from the scratch. But in many cases I do remember there exists a lot of Business Logic (technical : ETL-Layer) which extracts, transfoms and loads source-data into a common analytical database. Sometimes they call it Data Warehouse. ;-)

                  So you do not want to loose this knowledge in this case, sometimes you won't change a running system and get your data further on from stored procs. Best example:


                  RobertKarneus wrote:IT would require a very complex script to reproduce all the Stored Procedures in Qlikview ... . The SP's are not very well documented andI have not written them myself.


                  Hello Robert,

                  this points into a good direction. Nevertheless I am not a friend of using macros. What about writing the parameters directly (users) into e.g. excel-files? The trigger should be realized QV-external in any case, as far as I know the QV-Publisher can't be triggered by exist/update a file.

                  RR