14 Replies Latest reply: Oct 17, 2014 7:13 AM by iTree Consulting RSS

    Store procedure with parameters

    iTree Consulting

      Hi,

       

      I have a procedure with 5 parameters, Now I want to execute it in QlikView and want to Store all the records in QVD. I am using below the following script but it is giving error. So please 'let me know how it can be resolve.

       

      Error is :


       

       

      Script Which I am using in QlikView Script:

       

      usp_EPCM_Get_MultipleSearch:

      SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

      @LotID varchar(MAX)='262,260',

      @FromDate varchar(150)='',               

      @ToDate varchar(150)='',                            

      @JewlexRapPriceDate datetime = '01-Apr-2014',

      @GIARapPriceDate datetime = '01-Apr-2014';

      Store usp_EPCM_Get_MultipleSearch into E:\Amit Kumar\EPCM\usp_EPCM_Get_MultipleSearch.Qvd;


      Thanks

        • Re: Store procedure with parameters
          Peter Cammaert

          Strange. A typical Stored Procedure call should look like this:

           

          usp_EPCM_Get_MultipleSearch:

          SQL EXEC [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch] '262.260', '', '', '01-Apr-2014', '01-Apr-2014';

          STORE usp_EPCM_Get_MultipleSearch INTO ...


          Now, the syntax for everything between SQL and the semicolon should follow T-SQL rules, as it will be sent to the DB Engine just as if it came from a native SQL Server query. Your RDBMS is complaining that the calling format isn't correct, but that's more of a topic for a SQL Server forum...

            • Re: Re: Store procedure with parameters
              iTree Consulting

              Hi Peter,

               

              Thanks for reply...

               

              Please let me know why it is storing in my defined path.

               

              Error is :

               

                • Re: Store procedure with parameters
                  Peter Cammaert

                  The message says that the Reload engine cannot find the table to store. Most probably this is because the SQL EXEC returns 0 rows. In that case QlikView decides to not create a internal table called usp_EPCM_Get_MultipleSearch  and therefor the STORE statement will fail.

                    • Re: Store procedure with parameters
                      iTree Consulting

                      Is there any way to store the same SP information in QVD.

                        • Re: Store procedure with parameters
                          Peter Cammaert

                          I don't understand what you mean with "SP information"? The only interface to an RDBMS is via SQL queries that are sent as-is to the DB engine. The SQL EXEC you are using is just another SQL statement but it differs from a regular SQL SELECT in that it doesn't instruct the RDBMS to go and look for data rows, it just instructs the RDBMS to execute a predefined routine (a piece of code) that is stored in the DB itself. You could replace an SQL EXEC by a (probably) very complex SQL query in your QV Load Script but that would let performance take a deep dive.

                           

                          A QVD is not a DB engine; it's just a file that stores a single table and is generated from an existing internal QV table by way of a single STORE statement. All this happens locally on your development/server platform  A QV therefor cannot be used to store database code, unfortunately.

                           

                          I think you're stuck with your Stored Procedure. But that's not a bad thing. You'll only need to figure out how to properly call the SP. Can you get in touch with the developer of the Stored Procedure?

                           

                          Peter

                  • Re: Store procedure with parameters
                    Tresesco B

                    Try removing comma from the number, like:

                    @LotID varchar(MAX)='262,260',

                    @LotID varchar(MAX)='262260',

                    • Re: Store procedure with parameters
                      Jonathan Dienst

                      Hi

                       

                      Remove the type definitions in the parameters, and conditionally store the results if data is returned:

                       

                      usp_EPCM_Get_MultipleSearch:

                      SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

                        @LotID = '262,260',

                        @FromDate = '',              

                        @ToDate = '',                           

                        @JewlexRapPriceDate = '01-Apr-2014',

                        @GIARapPriceDate = '01-Apr-2014';

                       

                      If Alt(NoOfRows('usp_EPCM_Get_MultipleSearch'), 0) > 0 Then

                        Store usp_EPCM_Get_MultipleSearch into E:\Amit Kumar\EPCM\usp_EPCM_Get_MultipleSearch.Qvd;

                      End If

                       

                      HTH

                      Jonathan

                        • Re: Store procedure with parameters
                          iTree Consulting

                          Hi Jonathan,

                           

                          Thanks for reply.

                           

                          Output is not coming.

                           

                           

                          But Same SP with same parameter it is giving 85 Rows in SQL.

                           

                          Where I am wrong please let me know, It will very helpful for me.

                           

                          Thanks

                            • Re: Store procedure with parameters
                              iTree Consulting

                              Now the final SP Code with all the predefined parameters. Please check the code.

                               

                              Is it correct or not ?

                               

                              usp_EPCM_Get_MultipleSearch:

                              Load

                              [AvgClarity1_j],

                              [AvgClarity1_p],

                              [AvgClarity1_v],

                              [AvgClarity2_p],

                              [AvgClarity2_v],

                              [AvgClarity3_j],

                              [AvgClarity3_p],

                              [AvgClarity3_v],

                              [AvgColor_F],

                              [AvgColor_j],

                              [AvgColor_p],

                              [AvgColor_v],

                              [Certification_j],

                              [Certification_v],

                              [CertifiedPer],

                              [Clarity_P],

                              [Clarity1_j],

                              [Clarity1_v],

                              [Clarity2_j],

                              [Clarity2_P],

                              [Clarity2_v],

                              [Clarity3_j],

                              [Clarity3_P],

                              [Clarity3_v],

                              [Clarity4_j],

                              [Clarity4_P],

                              [Clarity4_v],

                              [Clarity5_j],

                              [Clarity5_P],

                              [Clarity5_v],

                              [ClarityAvg1_F],

                              [ClarityCode1_j],

                              [ClarityCode1_v],

                              [ClarityCode2_j],

                              [ClarityCode2_v],

                              [ClarityCode3_j],

                              [ClarityCode3_v],

                              [ClarityID1_j],

                              [ClarityID1_v],

                              [ClarityID2_j],

                              [ClarityID2_v],

                              [ClarityID3_j],

                              [ClarityID3_v],

                              [Color_j],

                              [Color_P],

                              [Color_v],

                              [Color1_j],

                              [Color1_v],

                              [Color2_j],

                              [Color2_P],

                              [Color2_v],

                              [Color3_P],

                              [Color4_j],

                              [Color4_P],

                              [Color4_v],

                              [Color5_j],

                              [Color5_P],

                              [Color5_v],

                              [ColorCode1_j],

                              [ColorCode1_v],

                              [ColorCode2_j],

                              [ColorCode2_v],

                              [ColorCode3_j],

                              [ColorCode3_v],

                              [ColorID_j],

                              [ColorID_v],

                              [Concatenate_j],

                              [Concatenate_v],

                              [CPF_P],

                              [CPF2_P],

                              [CPF3_p],

                              [CPS_j],

                              [CPS_v],

                              [cps1_j],

                              [cps1_v],

                              [cps2_j],

                              [cps2_v],

                              [cps3_j],

                              [cps3_v],

                              [CPS7_j],

                              [CPS7_v],

                              [CPS8_j],

                              [CPS8_v],

                              [Criteria_Mst_Count1_j],

                              [Criteria_Mst_Count1_v],

                              [Criteria_Mst_Count2_j],

                              [Criteria_Mst_Count2_v],

                              [Criteria_Mst_Count3_j],

                              [Criteria_Mst_Count3_v],

                              [Date_j],

                              [Date_v],

                              [Diff_perc],

                              [DiffHPtoActual],

                              [DollerIncentive_PenaltytoParty],

                              [EsRR],

                              [EstimatedLabourValue_j],

                              [EstimatedLabourValue_v],

                              [EstimatedRoughValue_j],

                              [EstimatedRoughValue_v],

                              [FinalColorCode1],

                              [FinalResultasPerJW],

                              [finaltotal],

                              [Fluorescence_j],

                              [Fluorescence_v],

                              [FluorescenceID1_j],

                              [FluorescenceID1_v],

                              [Galaxy_j],

                              [Galaxy_v],

                              [GD],

                              [HPEstPurity],

                              [HPTOFINAL],

                              [HPTOPARTY],

                              [ID_j],

                              [ID_v],

                              [Incentive_Penalty],

                              [IncentivePenaltyParty],

                              [Invoice$],

                              [jlxprice1_j],

                              [jlxprice1_v],

                              [jlxprice1Police_j],

                              [jlxprice1Police_v],

                              [jlxprice2_j],

                              [jlxprice2_v],

                              [jlxprice2Police_j],

                              [jlxprice2Police_v],

                              [jlxprice3_j],

                              [jlxprice3_v],

                              [jlxprice3Police_j],

                              [jlxprice3Police_v],

                              [Labour],

                              [LabourName_j],

                              [LabourName_v],

                              [Lot_v],

                              [LotDetailID_j],

                              [LotDetailID_v],

                              [LotID_j],

                              [LotID_v],

                              [LotNo],

                              [lotno_j],

                              [LotNo_P],

                              [lotno_v],

                              [Mix],

                              [OneExPer],

                              [PacketNo_j],

                              [PacketNo_P],

                              [PacketNo_v],

                              [PARTYTOFINAL],

                              [PolishCertWt],

                              [PolishNonCerWt],

                              [PolishWeight_P],

                              [PolishWeight1_j],

                              [PolishWeight1_v],

                              [PolishWeight2_j],

                              [PolishWeight2_P],

                              [PolishWeight2_v],

                              [PolishWeight3_j],

                              [PolishWeight3_P],

                              [PolishWeight3_v],

                              [PolishWeight4_j],

                              [PolishWeight4_v],

                              [PolishWeight5_j],

                              [PolishWeight5_P],

                              [PolishWeight5_v],

                              [PolishWeight6_P],

                              [RapPrice1_j],

                              [RapPrice1_v],

                              [RapPrice2_j],

                              [RapPrice2_v],

                              [RapPrice3_j],

                              [RapPrice3_v],

                              [Rate1_j],

                              [Rate1_v],

                              [Rate1Police_j],

                              [Rate2_j],

                              [Rate2_v],

                              [Rate2Police_j],

                              [Rate3_j],

                              [Rate3_v],

                              [Rate3Police_j],

                              [Rate7_j],

                              [Rate7_v],

                              [Rate8_j],

                              [Rate8_v],

                              [Rate9_P],

                              [Rate10_P],

                              [RoughRetWt],

                              [RoughWeight_j],

                              [RoughWeight_v],

                              [sarintotal],

                              [Shape_j],

                              [Shape_v],

                              [ShapeID_j],

                              [ShapeID_v],

                              [Size1_j],

                              [Size1_v],

                              [ThreeExPer],

                              [TotalJwelex],

                              [Totalpolise],

                              [TotalPolish],

                              [TotalVendor],

                              [TotalVendor_f],

                              [UploadType_j],

                              [UploadType_v],

                              [Value1_j],

                              [Value1_j_Polish],

                              [Value1_v],

                              [Value2_j],

                              [Value2_j_Polish],

                              [Value2_v],

                              [Value3_j],

                              [Value3_j_Polish],

                              [Value3_v],

                              [Value7_j],

                              [Value7_v],

                              [Value8_j],

                              [Value8_v],

                              [Value9_P],

                              [Value10_P],

                              [vendortotal],

                              [VG],

                              [VGXX];

                               

                              SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

                              @LotID = '262',

                              @FromDate = '',             

                              @ToDate = '',                         

                              @JewlexRapPriceDate = '01-Apr-2014',

                              @GIARapPriceDate = '29-Sep-2014';

                               

                              If Alt(NoOfRows('usp_EPCM_Get_MultipleSearch'), 0) > 0 Then

                               

                              Store usp_EPCM_Get_MultipleSearch into ...\usp_EPCM_Get_MultipleSearch.Qvd;

                               

                              End If

                               

                              Thanks