Skip to main content
D
djkhalif
Inspiring
September 24, 2009
Answered

Anyone? Store Procedures

  • September 24, 2009
  • 4 replies
  • 1287 views

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

This topic has been closed for replies.
Correct answer -__cfSearching__-

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 .....

4 replies

T
TLC-IT
Inspiring
September 25, 2009

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.

-
-__cfSearching__-
Inspiring
September 25, 2009

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.

D
djkhalifAuthor
Inspiring
September 27, 2009

cfsearching,

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

Thanks,

DJ Khalif    

D
djkhalifAuthor
Inspiring
September 24, 2009

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

-
-__cfSearching__-Correct answer
Inspiring
September 24, 2009

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 .....

D
djkhalifAuthor
Inspiring
September 25, 2009

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

-
-__cfSearching__-
Inspiring
September 24, 2009

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?

ilssac
ilssac
Inspiring
September 24, 2009

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices