9 Replies Latest reply on Sep 27, 2009 7:56 AM by djkhalif

    Anyone? Store Procedures

    djkhalif Level 1

      Good afternoon all,

       

      I created a stored procedure in SQL from:

      <cfquery name="rsDisposition" datasource="#REQUEST.datasource#">
      SELECT tlkp_Defects.Defect_Title,
        SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 1 then 1 else 0 end) FixedOnLine,
              SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 2 then 1 else 0 end) Repair,
              SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 3 then 1 else 0 end) Scrap
             
      FROM
          tbl_Assembly_Holds
          join tlkp_Disposition on tbl_Assembly_Holds.Disposition_ID = tlkp_Disposition.Disposition_ID
          join tbl_Assembly_Hold_Defects on tbl_Assembly_Hold_Defects.TagNumber = tbl_Assembly_Holds.TagNumber
          join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
      WHERE
        tbl_Assembly_Holds.Record_date Between '#FORM.dateFrom#' And '#FORM.dateTo#'
      GROUP BY
          tlkp_Defects.Defect_Title
      </cfquery>

       

       

      I am trying to call it from CF8 but I am getting errors, here is my code:

       

      <cfstoredproc procedure="defect_yield" datasource="#REQUEST.datasource#">
      <cfprocparam type = "IN"
         CFSQLType = CF_SQL_DATE
            value = "#FORM.dateFrom#">
            <cfprocparam type = "IN"
         CFSQLType = CF_SQL_DATE
            value = "#FORM.dateTo#">

      </cfstoredproc>

      Any ideas on how to make this work? I want convert most of queries to Stored Procedures.

       

      Thanks in advance,

       

      DJ Khalif

        • 1. Re: Anyone? Store Procedures
          ilssac Level 5

          What do you mean by "created a stored procedure in SQL form"?

           

          If your first <cfquery....> block was supposed to be a stored procedure....

           

          Every time I have ever seen stored procedure SQL code it started of with "CREATE OR REPLACE STORED PROCEDURE"....

           

           

          But since you gave such a vague description of your problem with no indication what errors you are experiencing or how your code is not working... I am really guessing on what the issue might be.

          • 2. Re: Anyone? Store Procedures
            -==cfSearching==- Level 4

            I created a stored procedure in SQL from:

            ....

             

            I am trying to call it from CF8 but I am getting errors,

             

             

            But what error are you receiving and what does the actual stored procedure sql look like?

            • 3. Re: Anyone? Store Procedures
              djkhalif Level 1

              Ian,

               

              I created the SP using the query in within the block. I verified in SQL 2005 by entering actual dates in '#FORM.dateFrom#' and ....

               

              Below is the error:

               

              Error Executing Database Query.

              [Macromedia][SQLServer JDBC Driver][SQLServer]Procedure defect_yield has no parameters and arguments were supplied.
              The error occurred in C:\inetpub\wwwroot\AtticaProducts\excelPages\dispositioncount.cfm: line 7
              5 :       <cfprocparam type = "IN"
              6 :    CFSQLType = CF_SQL_DATE
              7 :       value = "#FORM.dateTo#" dbVarName = @param2>
              8 : 
              9 : </cfstoredproc>
              

              SQLSTATE  HY000
              SQL  {call defect_yield( (param 1) , (param 2) )}
              VENDORERRORCODE  8146
              DATASOURCE  atDat

               

              SP:

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

              USE

               

               

              [productDetails]

              GO

              /****** Object: StoredProcedure [dbo].[defect_yield] Script Date: 09/24/2009 14:49:40 ******/

              SET

               

               

              ANSI_NULLS

              ON

              GO

              SET

               

               

              QUOTED_IDENTIFIER

              ON

              GO

              ALTER

               

               

              PROCEDURE [dbo].

              [defect_yield]

              AS

              SELECT

               

               

              productDetails.dbo.tlkp_Defects.Defect_Title,

               

               

              SUM(case WHEN productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = 1 then 1 else 0 end) FixedOnLine

              ,

               

               

              SUM(case WHEN productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = 2 then 1 else 0 end) Repair

              ,

               

               

              SUM(case WHEN productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = 3 then 1 else 0 end)

              Scrap

               

              FROM

              productDetails

               

              .dbo.

              tbl_Assembly_Holds

               

               

              join productDetails.dbo.tlkp_Disposition on productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = productDetails.dbo.tlkp_Disposition.

              Disposition_ID

               

               

              join productDetails.dbo.tbl_Assembly_Hold_Defects on productDetails.dbo.tbl_Assembly_Hold_Defects.TagNumber = productDetails.dbo.tbl_Assembly_Holds.

              TagNumber

               

               

              join productDetails.dbo.tlkp_Defects on productDetails.dbo.tbl_Assembly_Hold_Defects.Defect_ID = productDetails.dbo.tlkp_Defects.

              Defect_ID

              WHERE

              productDetails

               

              .dbo.tbl_Assembly_Holds.Record_date Between 'productDetails.dbo.tbl_Assembly_Holds.Record_date' And

              'productDetails.dbo.tbl_Assembly_Holds.Record_date'

              GROUP

               

               

              BY

              productDetails

               

              .dbo.tlkp_Defects.

              Defect_Title

              • 4. Re: Anyone? Store Procedures
                -==cfSearching==- Level 4

                Procedure defect_yield has no parameters and arguments were supplied.

                 

                ALTER PROCEDURE .[defect_yield]

                (No parameters defined here)

                AS

                SELECT

                 

                As the error message says, you are passing int two arguments (#FORM.dateFrom# and #FORM.dateTo#), but your procedure does not expect any. If you want to pass variable dates into the procedure, you need to add those parameters to your procedure declaration

                 

                ALTER PROCEDURE .[defect_yield]

                   @DateFrom DATETIME,  

                   @DateTo  DATETIME

                AS

                SELECT

                .....

                 

                WHERE productDetails

                .dbo.tbl_Assembly_Holds.Record_date Between

                'productDetails.dbo.tbl_Assembly_Holds.Record_date' And

                'productDetails.dbo.tbl_Assembly_Holds.Record_date'

                GROUP BY .....

                 

                Once you add the parameters, you can use those SQL variables within the procedure's query, just like in your original cfquery. More or less.

                 

                WHERE tbl_Assembly_Holds.Record_date Between @DateFrom  AND @DateTo .....

                • 5. Re: Anyone? Store Procedures
                  TLC-IT Level 3

                  Here's a tip...

                   

                  Always test your stored procedure by trying to call it within the SQL development-environment, exactly as you intend for ColdFusion to call it.  Make sure that it works as you intended, then address the now-separate issue of getting the ColdFusion call to work.

                  • 6. Re: Anyone? Store Procedures
                    djkhalif Level 1

                    cfsearching,

                     

                    That did it. I added the following:

                     

                    <cfstoredproc procedure="defect_yield" datasource="#REQUEST.datasource#">
                    <cfprocparam type = "IN"
                       CFSQLType = CF_SQL_DATE
                          value = "#FORM.dateFrom#" dbVarName = @DateFrom>
                          <cfprocparam type = "IN"
                       CFSQLType = CF_SQL_DATE
                          value = "#FORM.dateTo#" dbVarName = @DateTo>
                    <cfprocresult name = Defect>
                    </cfstoredproc>

                     

                    ...and it worked. I want to speed up my process time and portability of my application.

                     

                    Thanks.

                     

                    DJ Khalif

                    • 7. Re: Anyone? Store Procedures
                      -==cfSearching==- Level 4

                      djkhalif wrote:

                       

                      <cfprocparam type = "IN"

                         CFSQLType = CF_SQL_DATE
                            value = "#FORM.dateFrom#" dbVarName = @DateFrom>

                       

                      Do not use dbVarName, it is deprecated.  You must use positional notation.  ie The order of your cfprocparam statements must match the declaration of the variables in your stored procedure SQL or an error may occur.

                       

                      CFSQLType = CF_SQL_DATE

                      Assuming the forum did not mangle your code, you may want to add some quotes there. Just to keep the code clean.

                      • 8. Re: Anyone? Store Procedures
                        -==cfSearching==- Level 4

                        TLC-IT wrote:

                         

                        Always test your stored procedure by trying to call it within the SQL development-environment, exactly as you intend for ColdFusion to call it.  Make sure that it works as you intended, then address the now-separate issue of getting the ColdFusion call to work.

                        Sound advice.  There is no point adding CF to the mix if you are not even certain the procedure works as expected ... or at all.

                        • 9. Re: Anyone? Store Procedures
                          djkhalif Level 1

                          cfsearching,

                           

                          Before I attempted this is CF, I ran in SSMS but I had static dates. It worked.

                           

                          Thanks,

                           

                          DJ Khalif